如何在分组依据中使用案例陈述列 [英] How Do I Use Case Statement Column In Group By

问题描述

如问题所述，我试图制定一个查询，该查询在列结果中包含一个case语句，然后将该列包含在查询的group by语句中。举一个具体的例子，下面是我查询的全部内容：

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

在来到这里之前，我检查了一些网站，包括这一个 ，尝试解决我的问题。我尝试在 CASE 关键字之后添加另一个别名，如链接网页中所示，但没有运气。我继续收到的错误消息如下：

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 

是否还有其他人遇到问题我面对并能够在 CASE 语句的结果上使用 GROUP BY 吗？任何帮助，将不胜感激。哦，DB2版本是z / OS实例，版本10（DSN10015）

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)

推荐答案

别名不可使用在GROUP BY中，因为当GROUP BY发生时，别名尚未定义：

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

或者只使用在GROUP BY中的SELECT中使用的情况

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

这篇关于如何在分组依据中使用案例陈述列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！