It's been a while since I used Oracle, but did you try putting (+) next
to each right-side column in the join?
Another, not-as-good, solution might be to concat the two sides such as:
WHERE
CONCAT(pur.col1, '|', pur.col2, '|', pur.coln) =
CONTACT(prod.col1, '|', prod.col2, '|', prod.coln) (+)
m!ke
-----Original Message-----
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 20, 2008 9:06 AM
To: CF-Talk
Subject: Outer Join w/ multiple field key
In Oracle 7/8 without SQL 92 syntax.
I have the following query 'where' clause.
WHERE
(pur.chemical_no LIKE '%185' OR pur.chemical_no LIKE '%573') AND
pur.mfg_firm_no = prod.mfg_firmno AND
pur.label_seq_no = prod.label_seq_no AND
pur.revision_no = prod.revision_no AND
NVL2(pur.reg_firm_no,pur.reg_firm_no,pur.MFG_FIRM_NO) =
prod.reg_firmno AND
pur.commodity_code = site.site_code(+)
As you can see the join between the 'pur' table and the 'prod' table is
with a four part key; 'mfg_firm', 'label_seq_no', 'revision_no' and
'reg_firmno'. How would I make this an outer join with the Oracle (+)
syntax and not get incorrect results with this four part join? Do I
need to group this is some manner?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305711
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
