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
