SQL correlated subquery in a case clause

Question

Hello all, is it possible to write a subquery within a case clause for the when statement

ie.
SELECT colA, colB,
CASE WHEN (SELECT colA FROM tab2 WHERE tab2.colA = tab1.colA) THEN '1'
CASE WHEN (SELECT colA FROM tab3 WHERE tab3.colA = tab3.colA) THEN '2'
ELSE '0'
END AS colC,
...
FROM tab1

Extended question:
Is it possible to do something based on that value column? (pretty sure yes, but would like confirmation)
ie.

CASE
WHEN colC = '1' THEN ( select colR FROM...),
WHEN colC = '2' THEN (SELECT ColS FROM...),
ELSE 'doesn't work'
END AS colD

Furthermore, is the above case allowed to return multiple and different columns depending on which value colC is?
ie.
CASE
WHEN colC = '1' THEN ( select colR, colV, colX FROM...),
WHEN colC = '2' THEN (SELECT ColS, ColD FROM...),
ELSE 'doesn't work'
END AS colD

Thanks!

Answer 1

Case statements evaluate to a single value, so you cannot return multiple columns from them. You can use correlated sub-queries in your Where clause, though you don't show an example where you tried to use that. Whether or not colC will work in the Where Clause will depend on your Database Engine. I've worked with some that do, and others that require you to re-run the sub-query in the Where Clause.

Answer 2

is it possible to write a subquery within a case clause for the when statement

Yes. As g.d.d.c answered, it must be a subquery that returns a single value. That means zero or one rows and one column or value.

Is it possible to do something based on that value column?

Yes, with the same caveats as above.

Furthermore, is the above case allowed to return multiple and different columns depending on which value colC is?

No. The subquery must return zero or one row and only one column or value.

Answer 3

is it possible to write a subquery within a case clause for the when statement

I think this is what you are asking for:

SELECT colA, colB,
       CASE 
          WHEN EXISTS (SELECT * FROM tab2 WHERE tab2.colA = tab1.colA) 
             THEN '1'
          WHEN EXISTS (SELECT * FROM tab3 WHERE tab3.colA = tab3.colA) 
             THEN '2'
          ELSE '0'
       END AS colC
  FROM tab1;

---

Is it possible to do something based on that value column?

You CAN do this, which uses colA rather than colC in the second CASE expression:

SELECT colA, colB,
       CASE 
          WHEN EXISTS (SELECT * FROM tab2 WHERE tab2.colA = tab1.colA) 
             THEN '1'
          WHEN EXISTS (SELECT * FROM tab3 WHERE tab3.colA = tab3.colA) 
             THEN '2'
          ELSE '0'
       END AS colC, 
       CASE 
          WHEN colA = '1' THEN (SELECT colA FROM tab2)
          WHEN colA = '2' THEN (SELECT colB FROM tab3)
          ELSE 'doesn''t work'
       END AS colD
  FROM tab1;

[Note you would need to be careful about casting the result of the second CASE expression to a common data type, presumably VARCHAR considering the 'doesn''t work' default value.]

However, I think you are asking whether you can 're-use' the result of a CASE expression in the same SELECT clause, in this case colC. The answer to this is no, because the correlation name is not in scope**. You could of course wrap it in a subquery (or CTE, VIEW, etc):

SELECT DT1.colA, DT1.colB, DT1.colC, 
       CASE 
          WHEN DT1.colC = '1' THEN (SELECT colA FROM tab2)
          WHEN DT1.colC = '2' THEN (SELECT colB FROM tab3)
          ELSE 'doesn''t work'
       END AS colD
  FROM (       
        SELECT colA, colB,
               CASE 
                  WHEN EXISTS (SELECT * FROM tab2 WHERE tab2.colA = tab1.colA) 
                     THEN '1'
                  WHEN EXISTS (SELECT * FROM tab3 WHERE tab3.colA = tab3.colA) 
                     THEN '2'
                  ELSE '0'
               END AS colC
          FROM tab1
       ) AS DT1;

Note

** I'm basing my knowledge on Standard SQL rather than db2. MS Access, for example, allows you to use column correlation names in the same SELECT clause in a right-to-left manner but that just confirms that Access does not implement the SQL language!

---

is the above case allowed to return multiple and different columns depending on which value colC is

Different columns yes, multiple columns no. Think about it: the CASE expression returns a value so what data type would a value two columns be? Table, list, array, etc? Scalar values is a requirement for 1NF.