0

The following causes the above error:

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING > 1;

Here's the weird thing, the following query DOES work, the only difference being whether rating needs to be larger than or smaller than 1.

Suffice it to say that I'm utterly perplexed. Is this a bug? I'm on 11.2.0.4.0.

Could it be something to do with JARO_WINKLER?

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING < 1;

I partially solved it: PHONE is actually a VARCHAR2 column. However, this doesn't at all explain why comparing in one direction versus another works or doesn't work, when I make that mistake. So I'm still very curious.

3

Expressions in SELECT evaluated after filter[s] in WHERE applied. So if phones for records with RATING <1 can be converted to number, you don't have errors. Simplified example :

SELECT 
CASE WHEN n1=1 THEN 'a' ELSE 'b' END AS test_str
FROM 
(
SELECT '1a' AS n1 , 0 AS n2 FROM dual
UNION ALL
SELECT '1' AS n1 , 1 AS n2 FROM dual
)
x
WHERE n2 =0 -- gives an error
-- WHERE n2 =1 -  runs fine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.