I have an pretty ugly join to make. I need to join parts of a varchar
in one table to separate fields in the second. I can do this part ok,
but the kicker that has me stumped is that one apart is optional. This
is against an Oracle 8 dbms.
Here is the code I am using to break up and cast the parts of the
varchar field in the pur table and match it to fields in the pro table.
But the forth part should only happen if that part of the varchar string
is NOT a zero. Is there some way to make this part of the join conditional?
cast(substr(pur.epa_registration_num,0,7) AS number(7)) =
pro.mfg_firmno AND
cast(substr(pur.epa_registration_num,8,5) AS number(5)) =
pro.label_seq_no AND
cast(substr(pur.epa_registration_num,13,2) AS char(2)) =
pro.revision_no AND
cast(substr(pur.epa_registration_num,15,7) AS number(7)) =
pro.reg_firmno
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion is delivering applications solutions at at top companies
around the world in government. Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2952
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
