CODEWITHSUNDEEP
sqldb2rowtuples
davidhigh
davidhigh

Reputation: 15488

DB2 is there a tuple type?

I have implemented a long derivation logic in DB2 in which several variables get defined:

SELECT <other variables>
     , CASE
          <long logic, return variable_1>
       END AS variable_1
     , <...>
     , CASE
          <same long logic, return variable_n>
       END AS variable_n

FROM <...>

Instead of repeating the logic again and again, is it possible to use some form of tuple type (e.g., XML or TABLE data type) which would allow such a thing as the following?

SELECT <other variables>
     , CASE
          <long logic, return tuple of variables 1 to n>
       END AS variable_tuple       //example, doesn't work

FROM <...>

And if so, how can one access the tuple elements in subsequent steps?

Upvotes: 0

Views: 1277

Answers (1)

GarethD
GarethD

Reputation: 69789

As far as I know there is no tuple type. The way I tend to get around this is to move the long expression to a subuery, then create a table to join to with the different outcomes of the expresison, this allows multiple columns, e.g:

SELECT  expr.T1, expr.T2, expr.T3, COUNT(*) AS Count
FROM    (   SELECT  CASE WHEN ID < 100 THEN 1
                        WHEN ID < 200 THEN 2
                        WHEN ID < 300 THEN 3
                        WHEN ID < 400 THEN 4
                        WHEN ID < 500 THEN 5
                        WHEN ID < 600 THEN 6
                        WHEN ID < 700 THEN 7
                        WHEN ID < 800 THEN 8
                        WHEN ID < 900 THEN 9
                        ELSE 10
                    END AS Result
            FROM    T
        ) AS sq
        INNER JOIN
        (   SELECT  1 AS Result, 2 AS T1, 3 AS T2, 'test1' AS T3 UNION ALL
            SELECT  2 AS Result, 3 AS T1, 4 AS T2, 'test2' AS T3 UNION ALL
            SELECT  3 AS Result, 4 AS T1, 5 AS T2, 'test3' AS T3 UNION ALL
            SELECT  4 AS Result, 5 AS T1, 6 AS T2, 'test4' AS T3 UNION ALL
            SELECT  5 AS Result, 6 AS T1, 7 AS T2, 'test5' AS T3 UNION ALL
            SELECT  6 AS Result, 7 AS T1, 8 AS T2, 'test6' AS T3 UNION ALL
            SELECT  7 AS Result, 8 AS T1, 9 AS T2, 'test7' AS T3 
        ) AS expr
            ON expr.Result = sq.Result
GROUP BY expr.T1, expr.T2, expr.T3;

It is quite verbose, but it means the expression is only evaluated once, and it is fairly easy to add/remove rows from the derived table.

Upvotes: 2

Related Questions