993451 wrote:Any text other than '0', you mean. I assume it doesn't matter if that text happens to be all digits, such as '9876', or something with no digits, such as 'rare'.
I have table called NATIONAL_RARE_ECOSYSTEMS which has 1 column called TEST_COLUMN (data type: varchar2):
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:You're actually not doing any arithmetic, but you are comparing your VARCHAR2 column to a NUMBER, so it tries to convert the string to a NUMBER, and that's why you get the ORA-01722 error.
(CASE WHEN test_column is null THEN 0
WHEN test_column = 0 THEN 0
WHEN test_column > 0 THEN 1
END) AS NRE_SCORE
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?Here's one way:
I'm using oracle 11g.
Since you don't really care about the numeric value, don't use NUMBERs anywhere; stick with VARCHAR2s, such as '0'.
SELECT CASE WHEN NVL (test_column, '0') = '0' THEN 0 ELSE 1 END AS nre_score , ... -- you must want other columns, too FROM national_rare_ecosystems ;