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 any…

Accepted Answer

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

How Do I Use Case Statement Column In Group By

Solution

Problem