I am attempting a complex (to me at least) query that requires a number of left outer joins. I think I could do this by breaking it into 2 queries, but I would like to do it as one if possible.

The problem I am having is that whenever I try to add an extra outer join condition on a table that is already in a join, outer or otherwise, I get errors (from SQL Server - this is not vFp local) like:

Tables or functions 'sostrs' and 'sostrs' have the same exposed names. Use correlation names to distinguish them.

I know that the correlation name is just an alias. And I have tinkered endlessly with trying aliases to get it to work but can't.

In the code below, I get that error. If I take out the last join condition on sostrs and icunit, then I am fine. But I just can't seem to specify a join on condition for a table that is already named previously.
I even tried the syntax:

sostrs.cmeasure *= icunit.cmeasure in the where clause, but I got the error:

Outer join operators cannot be specified in a query containing joined tables.

Any help would be appreciated.


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(Icunit.cSymbol, space(10)) as cSymbol," + ;
" isnull(Arslpn.cName, space(35)) as cName, " + ;
" isnull(Icwhse.cDescript, space(35)) as cWhseDesc "

*" isnull(Icunit.cSymbol, space(10)) as cSymbol," + ;


lcFromClause= ;
" from " + ;
"icunit, " + ;
"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