Dzhara Dzhara - 1 month ago 7
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(A.name, (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 A.name and Value2 for those that don't. Like foreach loop but inside DECODE.

What is a proper way of doing it?

Update:
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:

Value1

Value1

Value2

Answer

In this situation you might use the CASE statement:

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