CODEWITHSUNDEEP
sqloracle-database
Satheesh K
Satheesh K

Reputation: 31

How to make this query run successfully?

select (SELECT * FROM TABLE(or_in_list_fn('FV52,1101'))) plant_cd from dual

returning

ORA-01427: single-row subquery returns more than one row

where or_in_list_fn returns
FV52
1101

I need Output

   Plant_cd
   ---------------
   FV52
   1101

Upvotes: 3

Views: 122

Answers (3)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

Try this: 

SELECT column_value AS plant_cd FROM TABLE(or_in_list_fn('FV52,1101'))

Upvotes: 1

podiluska
podiluska

Reputation: 51514

You need to make your subquery return 1 row and 1 field. You can use max() 

select (SELECT max(yourfield) FROM TABLE(or_in_list_fn('FV52,1101'))) plant_cd from dual

Upvotes: 0

Gaurav Soni
Gaurav Soni

Reputation: 6346

Rather than returning the data from another select ,directly use the inner query ,because you are not doing anything in Outside Select

SELECT * FROM TABLE(or_in_list_fn('FV52,1101'))

If you want to do some filter in the outer select or join with another table then use it in from clause of the query as shown below:

select plant_cd.* from (SELECT * FROM TABLE(or_in_list_fn('FV52,1101'))) plant_cd

Upvotes: 0

Related Questions