You have data in that field that is not a number.
You have made the classic mistake here of using a character string field to hold a number. SQL is non procedural - the query can and is rewritten for optimal performance. You seem to think that SQL is processed in "some order". I am not able guess what may be the problem. WHERE 681 >= SCD AND 681 SELECT a.* FROM (
When i execute the below SQL query from DEV Structure of the table are same in both the user, even data is also same. I have 2 users "dev" and "admin", both are in same database. So I'm still very curious.Could you please help me with the below SQL. However, this doesn't at all explain why comparing in one direction versus another works or doesn't work, when I make that mistake. I partially solved it: PHONE is actually a VARCHAR2 column. Is this a bug? I'm on 11.2.0.4.0.Ĭould it be something to do with JARO_WINKLER? SELECT * FROM ( Suffice it to say that I'm utterly perplexed. 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. UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY 8 THEN 6.57 ELSE -4.31 END as cityMatch,ĬASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE -2.16 END as FSAMatch, 85 then 8.46 else -.74 end as add2Match,Ĭase when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,Ĭase when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,Ĭase when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,ĬASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) >. 85 then 9.23 else -1.32 end as add1Match,Ĭase 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 7.97 else -1 end as nameMatch,Ĭase when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) >. (SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) >. 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 The following causes the above error: SELECT * FROM (