在配置单元中获取语义错误“表达式不在GROUP BY key 'Cust_intrl_ID‘中

Question

以下查询在第4行(表达式不在GROUP BY键‘Cust_intrl_ID’中)中有错误：

SELECT
    Cust_intrl_ID,
    customer_no,
    cust_type_cd
FROM
    (
        SELECT
            Cust_intrl_ID,
            customer_no,
            cust_cust_type_cd,
            year(trxn_exctn_dt) year,
            month(trxn_exctn_dt) month,
            sum(w.trxn_base_am)
        FROM
            (
                SELECT
                    cust.Cust_intrl_ID Cust_intrl_id,
                    regexp_replace(w.cstm_4_tx, "^0+(?!$)", "") customer_no,
                    year(w.trxn_exctn_dt) year,
                    month(w.trxn_exctn_dt) month,
                    sum(w.trxn_base_am) amt,
                    ca.cust_acct_role_cd cust_acct_role_cd,
                    cust.cstm_4_tx cstm_4_tx,
                    ca.acct_intrl_id acct_intrl_id
                FROM
                    l4_amlmntcbnn.Wire_trxn w
                    INNER JOIN l4_amlmntcbnn.acct a on a.eap_as_of_dt = '2021-01'
                    and a.Acct_intrl_ID = w.Benef_acct_ID
                    and a.acct_type1_cd <> 'IPB'
                    and substring(a.jrsdcn_cd, 0, 2) <> 'LA'
                    INNER JOIN l4_amlmntcbnn.cust_acct ca on ca.eap_as_of_dt = a.eap_as_of_dt
                    and ca.Acct_intrl_ID = a.Acct_intrl_ID
                    INNER JOIN l4_amlmntcbnn.cust on ca.eap_as_of_dt = cust.eap_as_of_dt
                    and ca.cust_intrl_ID = cust.cust_intrl_ID
                    and cust.cust_type_cd = 'IND'
                WHERE
                    w.trxn_exctn_dt between '2020-07-01'
                    and '2020-12-31'
                    and w.frgn_trxn_fl = 'Y'
                UNION ALL
                SELECT
                    cust.Cust_intrl_ID,
                    regexp_replace(w.cstm_4_tx, "^0+(?!$)", "") customer_no,
                    year(w.trxn_exctn_dt) year,
                    month(w.trxn_exctn_dt) month,
                    sum(w.trxn_base_am) amt,
                    ca.cust_acct_role_cd cust_acct_role_cd,
                    cust.cstm_4_tx cstm_4_tx,
                    ca.acct_intrl_id acct_intrl_id
                FROM
                    l4_amlmntcbnn.Wire_trxn w
                    INNER JOIN l4_amlmntcbnn.acct a on a.eap_as_of_dt = '2021-01'
                    and a.Acct_intrl_ID = w.Orig_acct_ID
                    and a.acct_type1_cd <> 'IPB'
                    and substring(a.jrsdcn_cd, 0, 2) <> 'LA'
                    INNER JOIN l4_amlmntcbnn.cust_acct ca on ca.eap_as_of_dt = a.eap_as_of_dt
                    and ca.Acct_intrl_ID = a.Acct_intrl_ID
                    INNER JOIN l4_amlmntcbnn.cust on ca.eap_as_of_dt = cust.eap_as_of_dt
                    and ca.cust_intrl_ID = cust.cust_intrl_ID
                    and cust.cust_type_cd = 'IND'
                WHERE
                    w.trxn_exctn_dt between '2020-07-01'
                    and '2020-12-31'
                    and w.frgn_trxn_fl = 'Y'
            ) w
        GROUP BY
            Cust_intrl_ID,
            regexp_replace(w.cstm_4_tx, "^0+(?!$)", ""),
            year(w.trxn_exctn_dt),
            month(w.trxn_exctn_dt),
            ca.cust_acct_role_cd,
            cust.cstm_4_tx,
            ca.acct_intrl_id
        having
            sum(w.trxn_base_am) >= 9000
    ) t
GROUP BY
    Cust_intrl_ID,
    customer_no,
    cust_acct_role_cd,
    cstm_4_tx,
    acct_intrl_id
having
    count(*) >= 5

Answer 1

这只是一种预感-但是group by子句试图对select子句中没有的列进行操作

SELECT
    Cust_intrl_ID,
    customer_no,
    cust_type_cd

..。

GROUP BY
    Cust_intrl_ID,
    customer_no,
    cust_acct_role_cd,
    cstm_4_tx,
    acct_intrl_id

另外，sum(w.trxn_base_am)没有别名-

作为一个旁注- count(*)可能不是这个查询的重点-但是如果您使用count(1)，您会发现性能有所提高