Pete,
Try using sequential JOINs rather than nested:

  FROM employee!elements
   INNER JOIN employee!elemtype ON Elements.claim_elem_code=elemtype.elem_code;
   INNER JOIN employee!wdntypes ON Elements.wdn_code=wdntypes.wdn_code;

HTH
>Hello All,
>can anyone tell me what's wrong with this code?,
>
>

>
>CREATE SQL VIEW "VIEW_ELEMENTS" ;
>   AS SELECT Elements.clm_ref, Elements.total_amt, Elements.agreed_amt,;
>  Elements.admitted_amt, Elements.setoff_amt, Elements.rejected_amt,;
>  Elements.multiplier_used, Elements.unquantified,;
>  Elements.date_element_received, Elements.claim_elem_code,;
>  Elements.claimstatus, Elements.excludingcosts, Elements.withdrawn,;
>  Elements.wdn_amt, Elements.wdn_date,;
>  Elements.adm_in_lux, Elements.id, Elements.potential,;
>  Elements.reject_date, Elements.rej_wave_no, Elements.appeal_date,;
>  Elements.struck_o_date, Elements.struck_o_desc, Elements.stg_let_st,;
>  Elements.setoff_date, Elements.agreed_date, Elements.admitted_date,;
>  Elements.stg_let_rc, Elements.in_rejection_amt,;
>  Elements.court_order_date, Elements.in_rejection_letter_date,;
>  Elements.in_rejection_letter_post_type,;
>  Elements.in_rejection_narrative, Elements.waiver_flag,;
>  Elements.waiver_date, Elements.appeal_expiry_date,;
>  Elements.appeal_expired_flag, Elements.appeal_expiry_term,;
>  Elements.litigant, Elements.luxrej_dt, Elements.location,;
>  Elements.amend_date, Elements.amend_id,Elements.wdn_code,;
>  Elements.total_amt-(Elements.agreed_amt+Elements.rejected_amt+Elements.in_rejection_amt+Elements.wdn_amt) AS pending_amt,;
>  .F. AS lchanged,;
>  VAL(TRANSFORM(IIF(Elements.agreed_amt>0,Elements.agreed_amt-Elements.setoff_amt,0),"999999999.99")) AS post_setoff,;
>  Elemtype.elem_desc, Wdntypes.wdn_desc ;
>  FROM employee!elements INNER JOIN employee!elemtype;
>    INNER JOIN employee!wdntypes ;
>   ON Elements.wdn_code = Wdntypes.wdn_code ;
>   ON Elements.claim_elem_code = Elemtype.elem_code ;
> WHERE Elements.clm_ref = ?Thisform.clm_ref ;
> ORDER BY Elemtype.elem_desc
>
>
>
>sorry the query is so lengthy, I know I can't create this view using the designer but !!!, why do I get the error message "SQL Column: WDN_CODE not found" when I try to create the view programatically?, this code worked fine until I added the third table "Wdntypes" into the equation, I posted a similar thread last week and learnt it was a failing of the view designer, so I duly coded this, and it doesn't work, the problematic field "WDN_CODE" is present in both tables and is of the same data type
>
>

>
>regards
>
>Pete Kane

Daniel