0

I have a table with following data, wanted to compare this table data with another table using "IN" clause, but throwing ORA-01722 error,

SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCOPE                                              VARCHAR2(20)
 PART_LIST                                          VARCHAR2(2000)

SQL> select part_list from a ;

PART_LIST
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16


SQL> select partition_name from dba_Tab_partitions where partition_position in (select trim((part_list) from a);

                                                                           
ERROR at line 1:
ORA-01722: invalid number

Regards Kannan

user9599919
  • 75
  • 7
  • You have to split the values in rows to use it in the In clause. Read: https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle – Simon Martinelli Mar 22 '21 at 15:22
  • yet another example (I've lost count for just the last week) of a fatally flawed data design. Multiple values in a single column violates the _very first_ rule of relational data design. It fails to meet even First Normal Form. Every one of those values should be in a row of their own, probably in a child table. Design your tables properly (to Third Normal Form) and your problem simply goes away. – EdStevens Mar 22 '21 at 19:53

1 Answers1

0

You need to first convert the comma-separated string into rows then you can use them like that. Try below query.

   select partition_name from dba_Tab_partitions where partition_position in (
   select  to_number(trim(regexp_substr(PART_LIST,'[^,]+', 1, level) )) value
   from a
   connect by regexp_substr(PART_LIST, '[^,]+', 1, level) is not null
   )
Tajinder
  • 2,248
  • 4
  • 33
  • 54