(oracle) SELECT (that returns more than one row) inside DECODE
<p>I'm trying to write a DECODE statement with SELECT inside it which will look like this:</p>

<pre><code>SELECT DECODE(A.name, (SELECT name FROM B WHERE id IN (1000,1001)),'Value1','Value2') FROM A
</code></pre>

<p>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. </p>

<p>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.</p>

<p>What is a proper way of doing it?  </p>

<p>Update:
Table A:</p>

<pre><code>+--------------+--------+
|Surname       | Name   |
+--------------+--------+
|           Abc| John   |
|         Smith| Piter  |
|          Cook| Ann    |
+--------------+--------+
</code></pre>

<p>Table B:</p>

<pre><code>+--------------+--------+
|ID           | Name   |
+--------------+--------+
|         1000| John   |
|         1001| Piter  |
|         2003| Ann    |
+--------------+--------+
</code></pre>

<p>What I expect to see:</p>

<p>Value1</p>

<p>Value1</p>

<p>Value2</p>