2 Replies Latest reply: Mar 20, 2013 5:25 PM by Frank Kulash RSS

    Invalid number error when using case when

    996454
      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
          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
            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