Sergey,

It is that last join condition that gives me an error. If I take it out, and take out the reference to icunit in the fields list, then it works. But, the problem is that I need that one last join. I need to join sostrs with icunit, but because sostrs is already in the join list, I can't 'name it again' without getting an error. I thought I always had to put the name of the 'left' table on the left as folows: 'leftablename' left outer join 'righttablename' on 'joincoindition' ?

I guess what would help me most, is if you could 'back out' the last fragment with sostrs and put back in a syntax that would work to add in a left join sostrs on icunit. I know that I could do it if put the result without it into a cursor - then redid the field list names with the name of the new cursor, and then did the join on icunit - but I would like to do it with one statement.

As far as the iciwhs.citemno+iciwhs.cwarehouse= sostrs.citemno+sostrs.cwarehouse is concerned, the compiler doesn't complain about it, and it runs. SQL Server seems to support the + operator in the statement. I haven't tested to make sure it returns the same results as doing it as:
iciwhs.citemno = sostrs.citemno AND iciwhs.cwarehouse= sostrs.cwarehouse

but I'll check. Am I confused here? Shouldn't these statements be equivalent?

Thanks for your help!!!



>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