ORA 01722: Invalid Number caused by different comparison

oracle

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.

Best Answer

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
Related Question