sql query help on linking tables

[ss7415]

i want to link my tables on two different criteria's.

im getting an error with this, but you can see what im trying to do

select a,qad.VENDOR,qad.PO_NBR,qad.PO_ITEM_NBR,qad.lot_nbr,qad.DATE_RCV,qad.PART,qad.ord_PART,qad.PROJECT_ONLY,qad.WO_NBR,qad.VDESC,qad.QTY_ORIG,qad.QTY_OPEN,qad.RCVR,
qty_onhand,qty_last_rcv

from qad left outer join idf_table idf on
(if qad.part is not null and idf.lot_nbr is not null then (qad.part = idf.part) and (qad.lot_nbr = idf.lot_nbr))
(if qad.ord_part is not null and qad.wo_nbr is not null then (qad.ord_part = idf.part) and (qad.a = qad.lot_nbr))

 

[PHV]

Have you tried this ?
...
FROM qad LEFT JOIN idf_table idf
ON (qad.part = idf.part AND qad.lot_nbr = idf.lot_nbr)
OR (qad.ord_part = idf.part AND qad.a = [!]qad[/!].lot_nbr)

The red part is really what you want ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886