I would guess you could rewrite the last bit as and (pur.reg_firmno is null OR pur.reg_firmno = pro.reg_firmno) or something similar.
Is that what you were asking? --Ben Doom Ian Skinner wrote: > I have inherited an ugly database design and need to make do the best I > can within SQL. > > I have to outer join two tables with a multi-part key, but the last part > is optional. Is there any reasonable way to do this in SQL? This is > against an Oracle (version 8 I believe) DBMS if that helps. The > following fields are involved in the join > > FROM > pur75raw pur, > product pro > > WHERE > pur.mfg_firmno = pro.mfg_firmno(+) AND > pur.label_seq_no = pro.seq_no(+) AND > pur.revision_no = pro.revision_no(+) AND > (IF pur.reg_firmno IS NOT NULL THEN pur.reg_firmno = > pro.reg_firmno(+)) -- This pseudo code or anything like it I have tried > does not work > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289561 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
