Message from: drapkin11
I ran the following query:
select
q1.ISO_COUNTRY_CODE q1country
,q3.ISO_COUNTRY_CODE q3country
,q1.LANGUAGE_CODE q1lang
,q3.LANGUAGE_CODE q3lang
,q1.POSTAL_CODE pcode1
,q3.POSTAL_CODE pcode3
,case when q1.POSTAL_CODE=q3.POSTAL_CODE then 1 else 0 end as “check”
from street3 q3
inner join street1 q1 on q1.PERM_ID=q3.PERM_ID
where
nvl(upper(q1.ISO_COUNTRY_CODE),‘tempnull’)=nvl(upper(q3.ISO_COUNTRY_CODE)
,‘tempnull’)
and
nvl(upper(q1.LANGUAGE_CODE),‘tempnull’)=nvl(upper(q3.LANGUAGE_CODE),‘tempnull’)
–and q1.POSTAL_CODE=q3.POSTAL_CODE
and rownum
;
Results were returned in less than a second:
Q1COUNTRY,Q3COUNTRY,Q1LANG,Q3LANG,PCODE1,PCODE3,check
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
… etc.
When I uncomment the line ‘and q1.POSTAL_CODE=q3.POSTAL_CODE’ in the
WHERE clause, however, the query takes forever to return. This doesn’t make
sense to me since the postcodes are equal in most cases anyway.
Historical Messages
Author: drapkin11
Date: Mon Jan 17 11:05:27 PST 2011
I ran the following query:
select
q1.ISO_COUNTRY_CODE q1country
,q3.ISO_COUNTRY_CODE q3country
,q1.LANGUAGE_CODE q1lang
,q3.LANGUAGE_CODE q3lang
,q1.POSTAL_CODE pcode1
,q3.POSTAL_CODE pcode3
,case when q1.POSTAL_CODE=q3.POSTAL_CODE then 1 else 0 end as “check”
from street3 q3
inner join street1 q1 on q1.PERM_ID=q3.PERM_ID
where
nvl(upper(q1.ISO_COUNTRY_CODE),‘tempnull’)=nvl(upper(q3.ISO_COUNTRY_CODE)
,‘tempnull’)
and
nvl(upper(q1.LANGUAGE_CODE),‘tempnull’)=nvl(upper(q3.LANGUAGE_CODE),‘tempnull’)
–and q1.POSTAL_CODE=q3.POSTAL_CODE
and rownum
;
Results were returned in less than a second:
Q1COUNTRY,Q3COUNTRY,Q1LANG,Q3LANG,PCODE1,PCODE3,check
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
… etc.
When I uncomment the line ‘and q1.POSTAL_CODE=q3.POSTAL_CODE’ in the
WHERE clause, however, the query takes forever to return. This doesn’t make
sense to me since the postcodes are equal in most cases anyway.
__