Invalid number error when using case when
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.