Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit
I am trying to select rows with a non-null value in one of two columns. Data is being selected from a view (MY_VIEW) with both columns defined as NUMBER types, like so:
COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, COLUMN_ID, COMMENTS, INSERTABLE, UPDATABLE, DELETABLE: ... num_1 NUMBER Yes 8 NO NO NO num_2 NUMBER Yes 9 NO NO NO
Query 1) works
select * from MY_VIEW where num_1 is null and num_2 is null; --OK, results below num_1 num_2 (null) (null) (null) (null) ...
Query 2) works
select to_number(num_1) as n1, to_number(num_2) as n2 from MY_VIEW; -- OK, results below n1 n2 (null) 1 (null) (null) (null) 0.32 (null) (null) (null) 15 (null) (null) 7.5 (null) 24 (null) (null) (null) ...
Query 3) FAILS with ORA-01722: invalid number
select * from MY_VIEW where num_1 is not null and num_2 is not null;
Query 4) Trying to exclude rows where both values are null, the following query also FAILS with ORA-01722: invalid number
select * from MY_VIEW where not (num_1 is null and num_2 is null);
The failed queries above indicate that there may be some invalid characters in numeric data, but attempts to convert to_char() and/or use regexp to find invalid numeric characters also fail with invalid number. Any suggestions on why queries 3 and 4 failed or tips on how to track down possible bad data would be very helpful. Thanks!
SOLVED Two bad rows of non-numeric data had been loaded into the view that loaded the view that loaded the view.