Finding the first row from a hardcoded list of column values (Pivot problem?)

Question

Hi,

I am trying to extract a column value from the "first" row that satisfies some criteria, where "first" is defined by a hard-coded list of column values. Unfortunately I'm a SQL amateur. I'm using DB2.

I can do it inefficiently like this:

SELECT COALESCE(
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'A'), 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'B'), 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'C'), 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'D') 
)

The problem here is that SOMETABLE is very big. So instead I would like to do something like:

SELECT COALESCE(
  (SELECT COL FROM T WHERE COL = 'A'), 
  (SELECT COL FROM T WHERE COL = 'B'), 
  (SELECT COL FROM T WHERE COL = 'C'), 
  (SELECT COL FROM T WHERE COL = 'D') 
)
FROM 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2") AS T

This is invalid, since I cannot reference the T table. Assume that T above contains the following rows:

'E'
'B'
'D'

Then I would like to pick 'B', because it is the first value specified in the COALESCE statement. I realise I can get somewhat nearer what I want by doing something like:

SELECT 
  (CASE COL WHEN 'A' THEN COL ELSE NULL END), 
  (CASE COL WHEN 'B' THEN COL ELSE NULL END), 
  (CASE COL WHEN 'C' THEN COL ELSE NULL END),
  (CASE COL WHEN 'D' THEN COL ELSE NULL END)
FROM 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2") AS T

This yields the result:

-   -   -   -
-   -   -   D
-   B   -   -

Now I would like to aggregate or "flatten" this into a single row like so:

-   B   -   D

And then I would simply coalesce on the columns.

Any suggestions would be greatly appreciated!

Answer 1

I think I found a solution.

SELECT COALESCE(
  MAX(CASE COL WHEN 'A' THEN COL ELSE NULL END), 
  MAX(CASE COL WHEN 'B' THEN COL ELSE NULL END), 
  MAX(CASE COL WHEN 'C' THEN COL ELSE NULL END),
  MAX(CASE COL WHEN 'D' THEN COL ELSE NULL END))
FROM 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2") AS T

Please let me know if and how this is stupid or wrong.

Answer 2

If I understand your question correctly, you can convert

SELECT COALESCE(  
    (SELECT COL FROM T WHERE COL = 'A'),   
    (SELECT COL FROM T WHERE COL = 'B'),   
    (SELECT COL FROM T WHERE COL = 'C'),   
    (SELECT COL FROM T WHERE COL = 'D') )
FROM   (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2") AS T

to CTE as under:

WITH T AS (
    SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2"
)
SELECT COALESCE(  
    (SELECT COL FROM T WHERE COL = 'A'),   
    (SELECT COL FROM T WHERE COL = 'B'),   
    (SELECT COL FROM T WHERE COL = 'C'),   
    (SELECT COL FROM T WHERE COL = 'D') )
FROM T;

or something like this. I can't test it as I don't have a DB2 database.

Answer 3

Providing your hard coded values sort in order appropriate to your question, then :

SELECT COL   
FROM SOMETABLE   
WHERE X = "txt1"   
AND Y = "txt2"   
ORDER BY col   
fetch first 1 rows only

Answer 4

You were pretty close with this…

SELECT COALESCE(
  MAX(CASE COL WHEN 'A' THEN COL ELSE NULL END), 
  MAX(CASE COL WHEN 'B' THEN COL ELSE NULL END), 
  MAX(CASE COL WHEN 'C' THEN COL ELSE NULL END),
  MAX(CASE COL WHEN 'D' THEN COL ELSE NULL END))
FROM 
  (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2") AS T

You don't need the coalesce (can't use this around multiple columns) and you don't need the subquery (the where clause is always evaluated first)

SELECT MAX(CASE WHEN COL = 'A' THEN COL ELSE NULL END) AS A, 
       MAX(CASE WHEN COL = 'B' THEN COL ELSE NULL END) AS B, 
       MAX(CASE WHEN COL = 'C' THEN COL ELSE NULL END) AS C,
       MAX(CASE WHEN COL = 'D' THEN COL ELSE NULL END) AS D
  FROM SOMETABLE 
 WHERE X = "txt1" 
   AND Y = "txt2"

Some databases support the ANSI SQL standard PIVOT function which does a similar (but not identical) thing. It doesn't sound like DB2/400 has many advanced features though.

Joe