DB2 restrict analytic function to subselect

Question

I often encountered the situation where I wanted to restrict the action of an analytic function (or OLAP in DB2) to a certain subset of the present data. Here is an example:

WITH MY_TABLE AS
(
     SELECT 1 AS FIELD1, 'A' AS FIELD2,  1 AS FIELD3, 'X' AS FIELD4 FROM DUAL
     UNION
     SELECT 1 AS FIELD1, 'A' AS FIELD2, 2 AS FIELD3, 'Y' AS FIELD4 FROM DUAL
     UNION
     SELECT 1 AS FIELD1, 'B' AS FIELD2, 3 AS FIELD3, 'X' AS FIELD4 FROM DUAL
     UNION
     SELECT 1 AS FIELD1, 'A' AS FIELD2, 4 AS FIELD3, 'Z' AS FIELD4 FROM DUAL
     UNION
     SELECT 1 AS FIELD1, 'B' AS FIELD2, 5 AS FIELD3, 'Z' AS FIELD4 FROM DUAL
     //...
)
SELECT FIRST_VALUE(FIELD4) OVER (PARTITION BY FIELD1 ORDER BY FIELD3) AS FIELD2_A
FROM MY_TABLE
WHERE FIELD2='A'  // need that as well for 'B', 'C' etc.

Now it is cumbersome to do this for all required values of FIELD2 and afterwards doing a JOIN or a UNION, moreover since the constraints might be more complicated.

Thus I really want to handle it in a single SELECT. Here is a trial:

//...
SELECT CASE WHEN FIELD2 = 'A'
            THEN FIRST_VALUE(FIELD4)
                       OVER (PARTITION BY FIELD1
                             ORDER BY CASE WHEN FIELD2='A' THEN 0 ELSE 1 END, FIELD3)
            ELSE NULL END AS FIELD4_A,
       CASE WHEN FIELD2 = 'B'
            THEN FIRST_VALUE(FIELD4)
                       OVER (PARTITION BY FIELD1
                             ORDER BY CASE WHEN FIELD2='B' THEN 0 ELSE 1 END, FIELD3)
            ELSE NULL END AS FIELD4_B,
      //a bit more complicated 
      CASE WHEN FIELD2 IN ('A','C')
            THEN FIRST_VALUE(FIELD4)
                       OVER (PARTITION BY FIELD1
                             ORDER BY CASE WHEN FIELD2 IN ('A','C') THEN 0 ELSE 1 END, FIELD3)
            ELSE NULL END AS FIELD4_AC
FROM MY_TABLE

The idea is to order the data set by the WHERE-criterion, but since there might be cases where rows with such a constraint do not exist in the partition (and thus the unwanted result would be obtained), a non-null value is returned only if the FIELD2 meets the constraint (this is done in the outer CASE statement).

I guess this approach works (--I haven't tested it yet), but it is a bit detoured. Are there easier or more direct approaches to accomplish the above task?

Answer 1

Why doesn't this do what you want?

SELECT FIRST_VALUE(FIELD4) OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD3) AS FIELD2_A
FROM MY_TABLE

If you want this pivoted into separate columns:

SELECT (CASE WHEN FIELD2 = 'A'
             THEN FIRST_VALUE(FIELD4) OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD3) 
        END) as FIELD4_A,
       (CASE WHEN FIELD2 = 'B'
             THEN FIRST_VALUE(FIELD4) OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD3) 
        END) as FIELD4_B,

EDIT:

I suspect that you want to pivot the "first" values. If it can never be NULL, then this might be what you are looking for:

SELECT FIRST_VALUE(CASE WHEN FIELD2 = 'A' THEN FIELD4 END IGNORE NULLS) OVER
           (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD3) as FIELD4_A,
       FIRST_VALUE(CASE WHEN FIELD2 = 'B' THEN FIELD4 END IGNORE NULLS) OVER
           (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD3) as FIELD4_B,
. . .