Dzhara Dzhara - 1 year ago 55
SQL Question

(oracle) SELECT (that returns more than one row) inside DECODE

I'm trying to write a DECODE statement with SELECT inside it which will look like this:

SELECT DECODE(, (SELECT name FROM B WHERE id IN (1000,1001)),'Value1','Value2') FROM A

Obviously if my select statement inside DECODE returns more than one row I will get an exception "ORA-01427: single-row subquery returns more than one row". Which is fair enough.

I'm expecting to get more than one row there, this is the purpose, and I need to display Value1 for all of the items that match and Value2 for those that don't. Like foreach loop but inside DECODE.

What is a proper way of doing it?

Table A:

|Surname | Name |
| Abc| John |
| Smith| Piter |
| Cook| Ann |

Table B:

|ID | Name |
| 1000| John |
| 1001| Piter |
| 2003| Ann |

What I expect to see:




Answer Source

In this situation you might use the CASE statement:

 in (select name from b where id in (1000, 1001)) then 'Value1' 
         ELSE 'Value2' 
 FROM a;