My first suggestion would be to switch to ANSI-style joins rather than
using clauses in the WHERE section.
ANSI joins look like
Select *
>From pur JOIN pro ON (<conditions here, including OR, etc>)
Where
<only restrctions on the already-joined tables go here>
There are some kinds of joins that can really only be expressed this
way, and your may be one of those.
Thanks
Mark
-----Original Message-----
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 26, 2007 5:34 PM
To: CF-Talk
Subject: Horrible condition join
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Download the latest ColdFusion 8 utilities including Report Builder,
plug-ins for Eclipse and Dreamweaver updates.
http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289614
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
