> Dear all,
>
> I would like to retrieve the desc of my origin, destination code joining with some other tables. My SQL as below but it kept prompting me invalid subquery. I tried to search from here and modified my query base on the suggestion but it still fail. Below are my 2 query which both also invalid.
>
> SELECT h.custcode+h.custcode+h.vslcode+h.voycode+h.cbound as BatchID, c.cust_cn, h.vslname, h.voycode, h.cbound, ; h.invdate, d.etapol, d.origin, d.pol, d.pod, d.deliveryp, ;
> d.blno, d.equiptype, d.qty, 'P' as freight, d.chrgcode, IIF(d.chrgccy = 'THC','CNY',d.chrgccy) as chrgccy, ; d.chrgrate, IIF(d.chrgccy = 'USD', d.amt, 0.00) as usdamt, IIF(d.chrgccy <> 'USD', d.amt, 0.00) as rmbamt ;
> FROM local_invh h INNER JOIN local_invd d ON h.invno = d.invno ;
> INNER JOIN (SELECT desc as origin FROM portcode) ON d.origin = portcode.portcode ;
> INNER JOIN (SELECT desc as dest FROM portcode) ON d.dest= portcode.portcode ;
> INNER JOIN customer c ON h.custcode = c.custcode ;
> WHERE h.invdate = lcInvDate ;
> ORDER BY BatchID, d.origin, d.pol, d.pod, d.deliveryp, d.blno ;
> INTO CURSOR ViewAttach
>
> SELECT h.custcode+h.custcode+h.vslcode+h.voycode+h.cbound as BatchID, c.cust_cn, h.vslname, h.voycode, h.cbound, ; h.invdate, d.etapol, d.origin, d.pol, d.pod, d.deliveryp, ;
> (SELECT desc FROM portcode WHERE d.origin = portcode.portcode) as origin, ;
> (SELECT desc as dest FROM portcode WHERE d.dest= portcode.portcode) as dest, ;
> d.blno, d.equiptype, d.qty, 'P' as freight, d.chrgcode, IIF(d.chrgccy = 'THC','CNY',d.chrgccy) as chrgccy, ; d.chrgrate, IIF(d.chrgccy = 'USD', d.amt, 0.00) as usdamt, IIF(d.chrgccy <> 'USD', d.amt, 0.00) as rmbamt ;
> FROM local_invh h INNER JOIN local_invd d ON h.invno = d.invno ;
> INNER JOIN customer c ON h.custcode = c.custcode ;
> WHERE h.invdate = lcInvDate ;
> ORDER BY BatchID, d.origin, d.pol, d.pod, d.deliveryp, d.blno ;
> INTO CURSOR ViewAttach
>
> Thanks.
>
> Regards,
> Venetia

INNER JOIN (SELECT portcode,desc as origin FROM portcode) as e ON d.origin = e.portcode 

Same with the other one - need to include your join condition in the select of the derived table.