Multiple Table Joins Syntax Problems

[nguenthe]

Here is the code i am trying to wokr through.
All seems to work fine until I incorporated the iCi table.
I cannot figure out how to set up the joins in the right order.
Can anyone please help?

SELECT i.itemnum, 
       i.description1, 
       i.udindx3                                  AS 'Customer', 
       c.slsp, 
       w.whse, 
       c.name, 
       w.onhandqty, 
       w.committedqty, 
       w.lastpurchdate, 
       w.lastsaledate, 
       w.avgcost, 
       w.wipqty, 
       ( w.onhandqty + w.wipqty )                 AS 'QOH+WIP', 
       ( ( w.onhandqty + w.wipqty ) * w.avgcost ) AS 'EXT_QOH', 
       w.avgleadtime 
FROM   domdata.pub.icwhseitem AS w 
       INNER JOIN (domdata.pub.icitem AS i 
                   INNER JOIN domdata.pub.arcustomer AS c 
                     ON ( i.udindx3 = c.custnum ) 
                        AND ( i.conum = c.conum ) 
                   LEFT JOIN domdata.pub.icinterchange AS ici 
                     ON ( i.conum = ici.conum ) 
                        AND ( i.itemnum = ici.itemnum ) 
                        AND ( i.udindx3 = ici.custnum ) 
                        AND ici.TYPE = '2') 
         ON ( w.itemnum = i.itemnum ) 
            AND ( w.conum = i.conum ) 
WHERE  ( ( ( w.whse ) = 'WH1' ) 
         AND ( ( w.onhandqty + w.wipqty ) > 0 ) )

 

[traingamer]

IS ici.TYPE text or numeric?

ici.TYPE = '2'

If numeric, remove the single quotes.

Or, more likely, you simply need to move that criteria from the LEFT JOIN to the WHERE clause:

LEFT JOIN domdata.pub.icinterchange AS ici 
                     ON ( i.conum = ici.conum ) 
                        AND ( i.itemnum = ici.itemnum ) 
                        AND ( i.udindx3 = ici.custnum )) 
                         
         ON ( w.itemnum = i.itemnum ) 
            AND ( w.conum = i.conum ) 
WHERE  ( ( ( w.whse ) = 'WH1' ) 
         AND ( ( w.onhandqty + w.wipqty ) > 0 ) AND [red][b](ici.TYPE = '2')[/b][/red] )

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard