Oracle SQL子查询外GROUP BY语法错误排查求助

我是Oracle SQL新手，目前正在学习相关知识，正尝试确定所需的GROUP BY用法。我的子查询单独执行时可以正常运行：

SELECT TO_CHAR(CREATE_DATE_TIME, 'DD-MON-YYYY')          "DTTM"  
                 , CASE_NBR
                 , COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID)             "REF_FIELD_1"
                 , COALESCE(PT.REF_FIELD_2, LPN.ASN_ID || LPN.ITEM_ID) "REF_FIELD_2"
            FROM PIX_TRAN PT, LPN
            WHERE ( ( PT.TRAN_TYPE = '300'
                      AND PT.TRAN_CODE = '01'
                      AND PT.ACTN_CODE = '20' )
                    OR ( PT.TRAN_TYPE = '300'
                         AND PT.TRAN_CODE = '04'
                         AND PT.ACTN_CODE = '21' ) )
                  AND SUBSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), 1, INSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), '_', 1)) != 'Return_'
                  AND PT.CASE_NBR = LPN.TC_LPN_ID (+)
                  AND PT.WHSE = 'DCV'
                  AND TRUNC(CREATE_DATE_TIME) = TRUNC(SYSDATE) 

但当我将其作为子查询并添加GROUP BY时，无法确定正确的GROUP BY写法：

SELECT 'PO Lines/LPNs Putaway'     AS "FACILITY_ACTIVITY"
     , TRUNC DTTM     AS "CREATED"
     , COUNT(DISTINCT REF_FIELD_1 || REF_FIELD_2)|| '/'|| COUNT(DISTINCT CASE_NBR) "Total"
FROM (      
            SELECT TO_CHAR(CREATE_DATE_TIME, 'DD-MON-YYYY')          "DTTM"  
                 , CASE_NBR
                 , COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID)             "REF_FIELD_1"
                 , COALESCE(PT.REF_FIELD_2, LPN.ASN_ID || LPN.ITEM_ID) "REF_FIELD_2"
            FROM PIX_TRAN PT, LPN
            WHERE ( ( PT.TRAN_TYPE = '300'
                      AND PT.TRAN_CODE = '01'
                      AND PT.ACTN_CODE = '20' )
                    OR ( PT.TRAN_TYPE = '300'
                         AND PT.TRAN_CODE = '04'
                         AND PT.ACTN_CODE = '21' ) )
                  AND SUBSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), 1, INSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), '_', 1)) != 'Return_'
                  AND PT.CASE_NBR = LPN.TC_LPN_ID (+)
                  AND PT.WHSE = 'DCV'
                  AND TRUNC(CREATE_DATE_TIME) = TRUNC(SYSDATE)
    )
GROUP BY TRUNC(DTTM);

我尝试了以下几种GROUP BY写法：

• 第一种尝试：

GROUP BY TRUNC(DTTM)

ERROR - "FROM Keyword not found where expected"

• 第二种尝试：
  修改SELECT子句为：

TRUNC(TO_CHAR(CREATE_DATE_TIME, 'DD-MON-YYYY')) AS "CREATED"

同时GROUP BY：

GROUP BY TRUNC(TO_CHAR(CREATE_DATE_TIME, 'DD-MON-YYYY'))

ERROR - "CREATE_DATE_TIME" invalid identifier

• 第三种尝试：
  修改SELECT子句为：

TRUNC(CREATE_DATE_TIME) AS "CREATED"

同时GROUP BY：

GROUP BY TRUNC(CREATE_DATE_TIME)

ERROR - "CREATE_DATE_TIME" invalid identifier

请问我忽略了什么问题？

问题分析与解决 #

错误原因拆解

1. 第一个错误：外层SELECT里的TRUNC DTTM语法错误，Oracle要求函数调用必须加括号，而且DTTM是字符串类型的日期，直接TRUNC会报错，需要先转回日期类型再截断。
2. 后两个错误：外层查询只能访问子查询返回的列别名，CREATE_DATE_TIME是子查询源表的列，外层无法直接引用，所以提示无效标识符。

正确写法示例

方案一：子查询返回截断后的日期（推荐）

直接在子查询里处理好日期截断，外层直接使用别名分组：

SELECT 'PO Lines/LPNs Putaway' AS "FACILITY_ACTIVITY"
     , CREATED AS "CREATED"
     , COUNT(DISTINCT REF_FIELD_1 || REF_FIELD_2) || '/' || COUNT(DISTINCT CASE_NBR) AS "Total"
FROM (      
    SELECT TRUNC(CREATE_DATE_TIME) AS CREATED
         , CASE_NBR
         , COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID) AS "REF_FIELD_1"
         , COALESCE(PT.REF_FIELD_2, LPN.ASN_ID || LPN.ITEM_ID) AS "REF_FIELD_2"
    FROM PIX_TRAN PT, LPN
    WHERE ( (PT.TRAN_TYPE = '300' AND PT.TRAN_CODE = '01' AND PT.ACTN_CODE = '20')
            OR (PT.TRAN_TYPE = '300' AND PT.TRAN_CODE = '04' AND PT.ACTN_CODE = '21') )
      AND SUBSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), 1, INSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), '_', 1)) != 'Return_'
      AND PT.CASE_NBR = LPN.TC_LPN_ID (+)
      AND PT.WHSE = 'DCV'
      AND TRUNC(CREATE_DATE_TIME) = TRUNC(SYSDATE)
)
GROUP BY CREATED;

方案二：外层转换字符串为日期再分组

如果需要保留子查询里的字符串日期格式，外层要先把字符串转回日期再截断：

SELECT 'PO Lines/LPNs Putaway' AS "FACILITY_ACTIVITY"
     , TRUNC(TO_DATE(DTTM, 'DD-MON-YYYY')) AS "CREATED"
     , COUNT(DISTINCT REF_FIELD_1 || REF_FIELD_2) || '/' || COUNT(DISTINCT CASE_NBR) AS "Total"
FROM (      
    SELECT TO_CHAR(CREATE_DATE_TIME, 'DD-MON-YYYY') AS "DTTM"
         , CASE_NBR
         , COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID) AS "REF_FIELD_1"
         , COALESCE(PT.REF_FIELD_2, LPN.ASN_ID || LPN.ITEM_ID) AS "REF_FIELD_2"
    FROM PIX_TRAN PT, LPN
    WHERE ( (PT.TRAN_TYPE = '300' AND PT.TRAN_CODE = '01' AND PT.ACTN_CODE = '20')
            OR (PT.TRAN_TYPE = '300' AND PT.TRAN_CODE = '04' AND PT.ACTN_CODE = '21') )
      AND SUBSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), 1, INSTR(COALESCE(PT.REF_FIELD_1, LPN.TC_ASN_ID), '_', 1)) != 'Return_'
      AND PT.CASE_NBR = LPN.TC_LPN_ID (+)
      AND PT.WHSE = 'DCV'
      AND TRUNC(CREATE_DATE_TIME) = TRUNC(SYSDATE)
)
GROUP BY TRUNC(TO_DATE(DTTM, 'DD-MON-YYYY'));

关键注意事项

• 外层查询只能使用子查询中定义的列别名，不能直接引用源表的原始列。
• Oracle函数调用必须严格使用括号包裹参数，语法不能省略。
• 字符串转日期时，格式必须和子查询中TO_CHAR的格式完全匹配，避免转换错误。

内容的提问来源于stack exchange，提问作者MarkP