I have a table 'A' with one column (VARCHAR2). The table contains a row containing the text '01/01/2021' and another row with the text 'A'.
When I try to filter out 'A' and then to_date the remaining value, I get 'ORA-01858: a non-numeric character was found where a numeric was expected'. I've tried this in 2 ways.
select *
from tbl
where col <> 'A'
and to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');
select *
from ( select *
from tbl
where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');
I can understand why the first might not work, but in the second example, the to_date should ONLY ever see filtered data (i.e. '01/01/2020').
When I delete the value of 'A', the statement runs and I get my result back so it seems conclusive that the reason it isn't running is because it's trying to to_date the value of 'A', even though that should have been filtered out by then.
I have been able to replicate this using actual Oracle tables but unfortunately when I try and reproduce the tables using WITH AS, the query works and no error is encountered - another mystery!
Why doesn't this query work? The order of operation seems to be satisfied (and it works if I use WITH AS).