Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Invalid number error when using case when

Daniel TaitMar 20 2013 — edited Mar 20 2013
I have table called NATIONAL_RARE_ECOSYSTEMS which has 1 column called TEST_COLUMN (data type: varchar2):

TEST_COLUMN
rare ecosystem
rare
0
0
(null)
(null)

what I want is a query which will add a column called NRE_SCORE which will give each row instance a score of 0 if it null.
If it is 0 then score should be 0.
If the row contains any text then score should be 1

I have written the query:

SELECT
(CASE WHEN test_column is null THEN 0
WHEN test_column = 0 THEN 0
WHEN test_column > 0 THEN 1
END) AS NRE_SCORE
FROM NATIONAL_RARE_ECOSYSTEMS;

I get the error message:

ORA-01722: invalid number
01722. 00000 - "invalid number"

I think this is because on the 2nd and 3rd line I'm trying to do arithmetic on a column which is varchar2 which I know I cant do.

How do I write a query which says: if the row contains text then give score of 1?

I'm using oracle 11g.
This post has been answered by Frank Kulash on Mar 20 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 17 2013
Added on Mar 20 2013
2 comments
1,836 views