title: (oracle) SELECT (that returns more than one row) inside DECODE
body: <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>