Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,775 Comments

Discussions

case when in a list - received ORA-01722 Invalid Number

Dee102
Dee102 Member Posts: 42 Green Ribbon

It received ORA-01722 Invalid Number at (select list_item_value from ListItem). ORA-01722 usually complains about an attempt is made to convert a character string into a number but I already used to_number in the Common Table Expression. Can someone please help? Thanks.


with ListItem as (

  select distinct to_number(list_item_value) list_item_value 

  from list_item 

  where list_name = 'XYZ'

)

select case

      when XYZ_NO in (select list_item_value from ListItem) then 1

      else 0

    end FLAG 

 from Table1

Answers

  • Paulzip
    Paulzip Member Posts: 8,422 Blue Diamond

    Describe list_item and Table1

  • mathguy
    mathguy Member Posts: 10,009 Gold Crown

    If column list_item_value in table list_item is of string data type, and some strings (at least one) cannot be converted to numbers, then wrapping the column within to_number() won't help.

    It is also possible that column XYZ_NO in Table1 is of string data type, and one or more of the strings in that column can't be converted to number.

    This is why Paulzip is asking you to "describe" the tables (show column data types, at least for the two columns I mentioned above).

    What happens if you try to run just this statement:

    select to_number(list_item_value) from list_item
    

    ? This is how you should go about diagnosing and fixing problems - try to reproduce the issue on smaller and smaller parts of your code. If you can't (for example if the statement above runs without problems), then you know the issue is somewhere else - try another small fragment of your code until you find the error again. Repeat.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,925 Red Diamond

    Hi, @Dee102

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

  • Dee102
    Dee102 Member Posts: 42 Green Ribbon

    Thanks for the responds.

    select to_number(list_item_value) from list_item
    

    works fine. list_item_value is string column but contains all numbers. XYZ_NO is number.

    It works if it is in the where clause.


    with ListItem as (

     select distinct to_number(list_item_value) list_item_value 

     from list_item 

     where list_name = 'XYZ'

    )

    select *

     from Table1

     where XYZ_NO in (select list_item_value from ListItem);

  • Dee102
    Dee102 Member Posts: 42 Green Ribbon

    The following works. Thank you everyone.

    with ListItem as (

     select distinct list_item_value 

     from list_item 

     where list_name = 'XYZ'

    )

    select case

         when to_char(XYZ_NO) in (select list_item_value from ListItem) then 1

         else 0

        end FLAG 

     from Table1

  • BluShadow
    BluShadow Member, Moderator Posts: 41,317 Red Diamond

    If that works, then that suggests that one of your "list_item_value" values couldn't be converted to a number as you originally thought. Are you sure you queried all the data and they were all able to be converted to numbers? Or does your interface only select the top N rows? (i.e. if I use TOAD, it only brings back the first 500 rows by default, so I'd have to tell it to scroll to the end to ensure all values are queried)

    Dee102