Sergey,

I'm not sure if my last post was confusing. So I am including code here that definitely works and gets me the result I want, but it does it with 2 statements not 1. Since it is working, I will probably just move on and not worry about it, but it would be nice to understand how to construct a single statement for it in the future. If it is not too much trouble could you let me know how, or if, I can do it with one select statement?

Thanks for your help! Here is the code:

lcFields1 = lcSortA + " as xSortBy, " + ;
"Sosord.dCreate, " + ;
"Sosord.cSlpnno, " + ;
"Sostrs.cSONo, " + ;
"Sostrs.cCustno, " + ;
"Sostrs.cWarehouse, " + ;
"Sostrs.cItemno, " + ;
"Sostrs.cDescript, " + ;
"Sostrs.cMeasure, " + ;
"Sostrs.dRequest, " + ;
"Sostrs.nDiscRate, " + ;
"Sostrs.nOrdQty, " + ;
"Sostrs.nShipQty, " + ;
"Sostrs.nPrice, " + ;
"Sostrs.nFPrice, " + ;
"Sostrs.nQtyDec, " + ;
"Sosord.cCurrCode, " + ;
"Sosord.nXChgRate, " + ;
"Sostrs.nreserved, " + ;
"SoStrs.ncancdamt, " + ;
"SoStrs.nsubdamt, "

lcMoreFields=;
" Arcust.cCompany, " + ;
" isnull(Iciwhs.nOnHand,000000000) as nOnHand, " + ;
" isnull(Iciwhs.nOnHand,000000000) as nOnOrder, " + ;
" isnull(Arslpn.cName, space(35)) as cName, " + ;
" isnull(Icwhse.cDescript, space(35)) as cWhseDesc "
*" isnull(Icunit.cSymbol, space(10)) as cSymbol," + ;

lcFromClause= ;
" from " + ;
"iciwhs " + ;
"left outer join sostrs on iciwhs.citemno+iciwhs.cwarehouse= sostrs.citemno+sostrs.cwarehouse " + ;
"inner join icwhse on iciwhs.cwarehouse=icwhse.cwarehouse, " + ;
"sosord " + ;
"left outer join Arslpn on sosord.cSlpnNo = Arslpn.cSlpnNo " + ;
" inner join arcust on sosord.ccustno = arcust.ccustno "

lcWhereClause= ;
" where " + ;
"Sostrs.nOrdQty > Sostrs.nShipQty and " + ;
"Sosord.lQuote <> 1 " + ;
" and sosord.csono = sostrs.csono "


lcSqlCmd='select ' + lcFields1+lcMoreFields+ lcFromClause + lcWhereClause

wait wind 'Please wait... retrieving data' nowait
close data all
open data data\sample
lnhandle=sqlconnect('vamconnect')
lnresult=sqlexec(lnhandle,lcSQlCmd, 'CurBord')
lnresult2=sqlexec(lnhandle,'select * from icunit', 'curicunit')

wait wind 'Retrieving icunit data ' nowait
select curbord.*, ;
curicunit.csymbol from curbord left outer join curicunit on curbord.cmeasure = curicunit.cmeasure
sqldisconnect(lnhandle)






**************************************************
>Hi David,
>
>I'm not sure why do you've ",sostrs" in following fragment
>"inner join arcust on sosord.ccustno = arcust.ccustno, " + ;
>"sostrs " + ;
>" left outer join icunit on sostrs.cmeasure = icunit.cmeasure "
>
>Also

"left outer join sostrs on iciwhs.citemno+iciwhs.cwarehouse= sostrs.citemno+sostrs.cwarehouse " + ;

looks like a VFP code. In Sql Server it should be

"left outer join sostrs on iciwhs.citemno = sostrs.citemno
>AND iciwhs.cwarehouse= sostrs.cwarehouse " + ;

>
>
>>In the code posted previously, icunit should not be in the from list. It should be:
>>>
>>>lcFromClause= ;
>>>" from " + ;
>>>"iciwhs " + ;
>>>"left outer join sostrs on iciwhs.citemno+iciwhs.cwarehouse= sostrs.citemno+sostrs.cwarehouse " + ;
>>>"inner join icwhse on iciwhs.cwarehouse=icwhse.cwarehouse, " + ;
>>>"sosord " + ;
>>>"left outer join Arslpn on sosord.cSlpnNo = Arslpn.cSlpnNo " + ;
>>>" inner join arcust on sosord.ccustno = arcust.ccustno, " + ;
>>>"sostrs " + ;
>>>" left outer join icunit on sostrs.cmeasure = icunit.cmeasure "
>>>
>>>
>>>lcWhereClause= ;
>>>" where " + ;
>>>"Sostrs.nOrdQty > Sostrs.nShipQty and " + ;
>>>"Sosord.lQuote <> 1 " + ;
>>>" and sosord.csono = sostrs.csono "
>>>
>>>
>>>
>>>lcSqlCmd='select ' + lcFields1+lcMoreFields+ lcFromClause + lcWhereClause