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.
If the last condition is really optional you should be able to just
leave it out :)
> Is there any reasonable way to do this in SQL? This is
> against an Oracle (version 8 I believe) DBMS if that helps.
Could you verify the exact version you have to work with? Because
elsewhere you are talking about SQL92 join syntax (which is A JOIN B ON
....) and that syntax was not introduced in Oracle until version 9.
> 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
You should be able to brute force it by using an unconstrained join
first and then filtering the result. In pseudocode:
SELECT *
FROM (
SELECT ...
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(+)
) AS tmp
WHERE
pur.reg_firmno = NULL
OR
pur.reg_firmno = pro.reg_firmno
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289596
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
