Anybody want to tackle or advise on this nightmare of a table join? Yes
this is a horrible design and I so wish it could be redone, but this
data is from 1974 to 1989 and it is not going to be changed anytime soon.
Oracle 7 DBMS
I need to join two tables, the issue is that in one table the foreign
key is a four part compound key. In the other table, the foreign key is
a single varchar field that concatenates the four parts into a single
field. A final twist is that the forth part is sometimes a value and
sometimes not, in which case the first value is repeated.
Here is an attempt to draw this out.
PRODUCT
mfg_firmno - number(7)
label_seq_no - number(5)
revision_no - char(2)
reg_firmno - number(7)
PUR
epa_registration_num - varchar(21)
This is a concatenation of the mfg_firmno zero padded to 7 characters,
the label_seq_no zero padded to 5 characters, the two character
revision_no and the reg_firmno zero padded to 7 characters if it exists,
otherwise a space padded '0'. When it is a zero the join needs to be
the mfg_firmno (the first seven characters) from the PUR table matched
to the reg_firmno in the PRODUCT table.
Examples:
000274900137AA0034704
004612500001AA0017545
000024100168AA 0
000026400135ZA 0
The proper joins should be:
PUR PRODUCT
epa_registration_num mfg_firmno label_seq_no
revision_no reg_firmno
000274900137AA0034704 = 2749 137
AA 34704
000024100168AA 0 = 241 168
AA 241
I have tried code such as this in various forms. It seems to work
somewhat initially, but when I start trying to bring other where clauses
and joins I start getting Cartesian squaring that improperly multiply my
returned records by several magnitudes.
SELECT
NVL(proWreg.product_name,proWOreg.product_name) AS product_name
FROM
pur89raw pur,
product proWreg,
product proWOreg
WHERE
cast(substr(pur.epa_registration_num,0,7) AS number(7)) =
proWreg.mfg_firmno AND
cast(substr(pur.epa_registration_num,8,5) AS number(5)) =
proWreg.label_seq_no AND
cast(substr(pur.epa_registration_num,13,2) AS char(2)) =
proWreg.revision_no AND
cast(substr(pur.epa_registration_num,15,7) AS number(7)) =
proWreg.reg_firmno AND
cast(substr(pur.epa_registration_num,0,7) AS number(7)) =
proWOreg.mfg_firmno AND
cast(substr(pur.epa_registration_num,8,5) AS number(5)) =
proWOreg.label_seq_no AND
cast(substr(pur.epa_registration_num,13,2) AS char(2)) =
proWOreg.revision_no AND
cast(substr(pur.epa_registration_num,0,7) AS number(7)) =
proWOreg.reg_firmno
Any help would be most gratefully accepted.
TIA
Ian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3049
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
