Re: Join one concatenated field to four fields
Sonny Savage wrote: > Ouch... doing conversions in the where clause is gonna' kill your > performance. Yeah, the good thing is that this only has to run once. This was not a web application requirement. It is a one time only, public data request that we get $50 for but costs us a couple hundred dollars in man hours to create. So down and dirty, but quick is the key concept. ~| 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/CF-Talk/message.cfm/messageid:301162 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Join one concatenated field to four fields
Ouch... doing conversions in the where clause is gonna' kill your performance. I'm not sure concatenations work in the where clause, but something like this might have better performance: WHERE ( pur.chemical_code LIKE '%185' OR pur.chemical_code LIKE '%573' ) AND ( pur.epa_registration_num = prod.mfg_firmno || prod.label_seq_no || prod.revision_no || prod.reg_firmno OR ( prod.reg_firmno = prod.mfg_firmno AND pur.epa_registration_num = prod.mfg_firmno || prod.label_seq_no || prod.revision_no || '0' ) ) On Wed, Mar 12, 2008 at 5:14 PM, Ian Skinner <[EMAIL PROTECTED]> wrote: > With much blood, sweat and tears and finally finding a source of help. > I know of this: > > SELECT >pur.epa_registration_num, >prod.mfg_firmno, >prod.label_seq_no, >prod.revision_no, >prod.reg_firmno > > FROM >pur89raw pur, >product prod > > WHERE >(pur.chemical_code LIKE '%185' OR pur.chemical_code LIKE '%573') AND > >cast(substr(pur.epa_registration_num,0,7) AS number(7)) = > prod.mfg_firmno AND >cast(substr(pur.epa_registration_num,8,5) AS number(5)) = > prod.label_seq_no AND >cast(substr(pur.epa_registration_num,13,2) AS char(2)) = > prod.revision_no AND > cast(decode(rtrim(substr(pur.epa_registration_num,15,7)),0, > substr(pur.epa_registration_num,0,7), > substr(pur.epa_registration_num,15,7)) AS number(7)) = prod.reg_firmno > > > > ~| 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/CF-Talk/message.cfm/messageid:301109 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Join one concatenated field to four fields
With much blood, sweat and tears and finally finding a source of help. I know of this: SELECT pur.epa_registration_num, prod.mfg_firmno, prod.label_seq_no, prod.revision_no, prod.reg_firmno FROM pur89raw pur, product prod WHERE (pur.chemical_code LIKE '%185' OR pur.chemical_code LIKE '%573') AND cast(substr(pur.epa_registration_num,0,7) AS number(7)) = prod.mfg_firmno AND cast(substr(pur.epa_registration_num,8,5) AS number(5)) = prod.label_seq_no AND cast(substr(pur.epa_registration_num,13,2) AS char(2)) = prod.revision_no AND cast(decode(rtrim(substr(pur.epa_registration_num,15,7)),0, substr(pur.epa_registration_num,0,7), substr(pur.epa_registration_num,15,7)) AS number(7)) = prod.reg_firmno ~| 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/CF-Talk/message.cfm/messageid:301104 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Join one concatenated field to four fields
I'm hoping this will receive a wider audience here in Talk. This is SQL for an Oracle 7 DBMS. I need to join a fixed 21 character field that looks like aTable.wwwxyyzzz where ww = bTable.field1 AND x = bTable.field2 AND yy = bTable.field3 AND zz = bTable.field4 unless zz is 0 then www = bTable.field 4. Uhg. I have this code, but I'm not sure what to do with it. WITHOUT reg_firmno: SELECT pur.epa_registration_num, prod.mfg_firmno, prod.label_seq_no, prod.revision_no, prod.reg_firmno FROM pur89raw pur, product prod WHERE (pur.chemical_code LIKE '%185' OR pur.chemical_code LIKE '%573') AND cast(substr(pur.epa_registration_num,0,7) AS number(7)) = prod.mfg_firmno AND cast(substr(pur.epa_registration_num,8,5) AS number(5)) = prod.label_seq_no AND cast(substr(pur.epa_registration_num,13,2) AS char(2)) = prod.revision_no AND cast(substr(pur.epa_registration_num,0,7) AS number(7)) = prod.reg_firmno 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/CF-Talk/message.cfm/messageid:301103 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
