0

Oracle Database 12c Enterprise Edition Release 12.1.0.1.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.

  • Are these two columns in the view selecting data from table(s) where the data is numeric? Or is the view selecting data from table(s) where the data is a varchar2 and you are implicitly or explicitly casting the data to a number in the view? Are there rows in the underlying table that have non-numeric data in those columns? – Justin Cave Aug 4 '14 at 21:00
  • Those number columns are selected as: null, varchar2(508 byte) * number, or to_number(varchar2(508 byte)) * number. – oracat Aug 4 '14 at 21:35
  • Are the varchar2(508 byte) columns expected contain non-numeric characters (i.e. you have some rows that have the string "foo" and other rows that have the string "1.25" and you expect that predicates in the view return only those strings that have numeric characters)? – Justin Cave Aug 4 '14 at 23:05

Your Answer

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

Browse other questions tagged or ask your own question.