How Do I Use Case Statement Column In Group By

Question

As stated by the question, I'm trying to formulate a query that has a case statement in the column results, and then I want to include that column in the query's group by statement. To give a concrete example, here is all little of what my query looks like:

SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END AS CASE_COLUMN
FROM SOME_TABLE SOME_TABLE_ALIAS
... (other table joins and where clauses)
GROUP BY SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2, CASE_COLUMN

Before coming here, I checked out a few websites, including this one, to try solve my problem. I've tried adding another alias after the CASE keyword like is shown in the linked web page but have had no luck. The error message I continue to receive is the following:

[Error] Script lines: 127-151 ----------------------
 CASE_COLUMN IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.53.71 

Has anyone else run into the issues I'm facing and been able to use a GROUP BY on the results of a CASE statement? Any help would be appreciated. Oh, and the DB2 version is a z/OS instance, version 10 (DSN10015)

Answer 1

You can either use the case as is in the group by, like this:

SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END AS CASE_COLUMN
FROM SOME_TABLE SOME_TABLE_ALIAS
... (other table joins and where clauses)
GROUP BY SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2, 
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END

or use a sub-query like this:

select COLUMN1, COLUMN2, CASE_COLUMN
from (
    SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
    CASE
        WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
        ELSE 'B'
    END AS CASE_COLUMN
    FROM SOME_TABLE SOME_TABLE_ALIAS
    ... (other table joins and where clauses)
) a
GROUP BY COLUMN1, COLUMN2, CASE_COLUMN

Answer 2

The alias isn't available to use in the GROUP BY because when GROUP BY happens the alias isn't defined yet:

Here's the order:
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY

You can work around that with:

SELECT column1,column2,case_column
FROM (
SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END AS CASE_COLUMN
FROM SOME_TABLE SOME_TABLE_ALIAS
... (other table joins and where clauses)
) a
GROUP BY COLUMN1, COLUMN2, CASE_COLUMN

Or just use the case you use in SELECT in GROUP BY