First, you are missing a semi-colon on the line before the HAVING clause. Can you create the view programmatically without error? Like:

CREATE SQL VIEW MyView AS ...Select Statement Here...

If a remote view, add CONNECTION clause to above command. If the view creates successfully, can you then open that view? Don't forget to initialize the view parameter before opening the view if you do not want to be prompted for it.

>Hi,
>What wrong with this sql? It works in command window, but cause error "SQL column 'iiD' is not found" from view designer and dataenvironment.
>
>

>SELECT Patient_treatments.memcode, Patient_treatments.iid,;
>  Patient_treatments.itreatid, Patient_treatments.ddatetime,;
>  Patient_treatments.nqty, Patient_treatments.yprice,;
>  Patient_treatments.ncount, Treatments.ccode, Treatments.cdesc,;
>  SUM( Consultancydetails.iqty) AS nUsed;
> FROM  physician!treatments INNER JOIN physician!patient_treatments;
>    INNER JOIN physician!consultancydetails;
>    INNER JOIN membership!member;
>    RIGHT OUTER JOIN physician!consultancydetails ;
>   ON  Consultancydetails.itreatid = Patient_treatments.iid ;
>   ON  Patient_treatments.memcode = Member.card_no ;
>   ON  Treatments.iid = Consultancydetails.itreatid ;
>   ON  Treatments.iid = Patient_treatments.itreatid;
> WHERE Member.card_no = ?vp_memcode;
> GROUP BY Patient_treatments.iid, Patient_treatments.memcode,;
>  Patient_treatments.itreatid, Patient_treatments.ddatetime,;
>  Patient_treatments.nqty, Patient_treatments.yprice,;
>  Patient_treatments.ncount
> HAVING nUsed < Patient_Treatments.nCount
>

>
>Please advise
>Thank you