1

Good morning, I have a problem in plsql because I have to split a string which has to be put in a where condition of a select as in the following example:

str:='3118450,3118451,3118449,3118447,3118448';

select T500DDT.T500DELIVERYCODE into x
    from t500testadoc t500ddt
    where T500DDT.T500DELIVERYCODE in (select regexp_substr(replace(str, ' ', ''),'[^,]+', 1, level) from dual
         connect by regexp_substr(replace(str, ' ', ''), '[^,]+', 1, level) is not null)
    group by  T500DDT.T500DELIVERYCODE ;

but The result is :

ORA-01422: exact fetch returns more than requested number of rows

The problem is how can I put the splitted string in the condition of where

select T500DDT.T500DELIVERYCODE into x
from t500testadoc t500ddt
where T500DDT.T500DELIVERYCODE in (???)-- here there should be the splitted string 
group by  T500DDT.T500DELIVERYCODE ;

Thanks for the help.

default locale
  • 13,035
  • 13
  • 56
  • 62
user3868055
  • 61
  • 5
  • 1
    Oracle exception is thrown because your query returns multiple rows, and you can't put multiple values `into x`. Probably you should take some specific value, like `MIN(T500DDT.T500DELIVERYCODE)`. `where` clause is not the root of a problem here. – default locale Jul 25 '14 at 07:37
  • 1
    I believe your question is already covered on StackOverflow. First part: [ORA-01422](http://stackoverflow.com/a/7067614/451518). Second part: alternative methods [to split a string in pl/sql](http://stackoverflow.com/q/3710589/451518) – default locale Jul 25 '14 at 07:47
  • I resolved this problem deleting group by and inserting MIN in the query as the following: str:='3118450,3118451,3118449,3118447,3118448'; select MIN(T500DDT.T500DELIVERYCODE) into x from t500testadoc t500ddt where T500DDT.T500DELIVERYCODE in (select regexp_substr(replace(str, ' ', ''),'[^,]+', 1, level) from dual connect by regexp_substr(replace(str, ' ', ''), '[^,]+', 1, level) is not null); – user3868055 Jul 25 '14 at 07:55

0 Answers0