This discussion is archived
2 Replies Latest reply: Mar 20, 2013 3:25 PM by Frank Kulash RSS

Invalid number error when using case when

996454 Newbie
Currently Being Moderated
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.
  • 1. Re: Invalid number error when using case when
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    993451 wrote:
    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
    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 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.
    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.
    >
    How do I write a query which says: if the row contains text then give score of 1?

    I'm using oracle 11g.
    Here's one way:
    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
    ;
    Since you don't really care about the numeric value, don't use NUMBERs anywhere; stick with VARCHAR2s, such as '0'.

     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
    Point out where the query above is getting the wrong results, and explain, using specific examples, how you get those results from the sample data in those palces.
    See the forum FAQ {message:id=9360002}
  • 2. Re: Invalid number error when using case when
    996454 Newbie
    Currently Being Moderated
    Awesome! It works great - thanks.


    Frank Kulash wrote:
    SELECT     CASE
         WHEN NVL (test_column, '0') = '0'
         THEN 0
         ELSE 1
         END          AS nre_score
    ,     ...          -- you must want other columns, too

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points