java - ORA-00979 不是带有 case 的 group by 表达式

Question

获取ORA-00979不是按表达式分组我找不到问题。

所有选择的参数都显示在分组中:

select case when :contract_number like '001-CR-%' then t.PARTNER_NAME else t.OPPORTUNITY_NAME end merchant_name,
       case when :contract_number like '001-CR-%' then t.ACQ_CONTRACT_NUM_PRT else t.ACQ_CONTRACT_NUM_MER end contract_id, 
       t.target_mid_id as terminal_number,
       t.ACQ_POSTING_DATE as posting_date,
       'Processing Activity' category, 
       case when ty.CREDORAX_TRX_TYPE='Refund' then 'Refund (Credit)' else ty.CREDORAX_TRX_TYPE end as TRX_TYPE_DESC, 
       cu.CURRENCY_ID,
       sum(t.SETTLE_AMOUNT_ORG) as "SUM(T.GROSS_AMOUNT_ORG)",
       count(*)
from tmp_proc t
join DWH.DIM_TRX_TYPE ty on t.TRX_TYPE_KEY = ty.TRX_TYPE_KEY
join tmp_currency cu on cu.CURRENCY_KEY = t.SETTLE_CURRENCY_KEY


group by case when :contract_number like '001-CR-%' then t.PARTNER_NAME else t.OPPORTUNITY_NAME end,
       case when :contract_number like '001-CR-%' then t.ACQ_CONTRACT_NUM_PRT else t.ACQ_CONTRACT_NUM_MER end, 
       t.target_mid_id,
       t.ACQ_POSTING_DATE,
       'Processing Activity', 
       case when ty.CREDORAX_TRX_TYPE='Refund' then 'Refund (Credit)' else ty.CREDORAX_TRX_TYPE end, 
       cu.CURRENCY_ID

Answer 1

您可以使用子查询来简化查询，如下所示:

SELECT
    MERCHANT_NAME,
    CONTRACT_ID,
    TERMINAL_NUMBER,
    POSTING_DATE,
    CATEGORY,
    TRX_TYPE_DESC,
    CURRENCY_ID,
    SUM(SETTLE_AMOUNT_ORG) AS "SUM(T.GROSS_AMOUNT_ORG)",
    COUNT(*)
FROM
    (
        SELECT
            CASE
                WHEN :CONTRACT_NUMBER LIKE '001-CR-%' THEN T.PARTNER_NAME
                ELSE T.OPPORTUNITY_NAME
            END MERCHANT_NAME,
            CASE
                WHEN :CONTRACT_NUMBER LIKE '001-CR-%' THEN T.ACQ_CONTRACT_NUM_PRT
                ELSE T.ACQ_CONTRACT_NUM_MER
            END CONTRACT_ID,
            T.TARGET_MID_ID      AS TERMINAL_NUMBER,
            T.ACQ_POSTING_DATE   AS POSTING_DATE,
            'Processing Activity' CATEGORY,
            CASE
                WHEN TY.CREDORAX_TRX_TYPE = 'Refund' THEN 'Refund (Credit)'
                ELSE TY.CREDORAX_TRX_TYPE
            END AS TRX_TYPE_DESC,
            CU.CURRENCY_ID,
            T.SETTLE_AMOUNT_ORG
        FROM
            TMP_PROC T
            JOIN DWH.DIM_TRX_TYPE TY ON T.TRX_TYPE_KEY = TY.TRX_TYPE_KEY
            JOIN TMP_CURRENCY CU ON CU.CURRENCY_KEY = T.SETTLE_CURRENCY_KEY
    )
GROUP BY
    MERCHANT_NAME,
    CONTRACT_ID,
    TERMINAL_NUMBER,
    POSTING_DATE,
    CATEGORY,
    TRX_TYPE_DESC,
    CURRENCY_ID

干杯!!