SQL Server 列ウx Ж在选择列表中无效,因为它未包含在聚合函数或GROUP BY子句中,有关如何解决此问题,请参阅

yzckvree  于 2022-11-28  发布在  其他
关注(0)|答案(1)|浏览(289)

我正在创建一个存储过程。当我给一个表提供连接时,它显示错误:
列“Transaction_Type.Transaction_Type”在选择列表中无效,因为它未包含在聚合函数或GROUP BY子句中.
我的选择查询:

DECLARE @newImportId varchar = '1';
DECLARE @fileid varchar = '1';

SELECT als.Division,
       als.Date_Auth,
       '', --,CASE WHEN als.mop_code= '' THEN 'XX'  ELSE als.mop_code END
       (CASE
             WHEN als.CARD_TYPE = 'CREDIT' THEN '1'
             WHEN als.CARD_TYPE = 'DEBIT' THEN '2'
             WHEN als.CARD_TYPE = 'CHARGE CARD' THEN '6'
             ELSE '99'
        END),
       als.acquirer_Auth_response_code,
       CASE WHEN als.auth_count = '' THEN 0 ELSE SUM(CONVERT(int, als.auth_count))END,
       CASE WHEN als.auth_amount = '' THEN 0 ELSE SUM(CONVERT(decimal(18, 2), als.auth_amount))END,
       CASE WHEN als.TXN_TYPE = 'Purchase' THEN 'AU' WHEN als.TXN_TYPE = 'Return' THEN 'Refund' END,
       @newImportId,
       '13-' + als.acquirer_Auth_response_code,
       CASE WHEN als.acquirer_avs_response_code = '' THEN 'Uk' ELSE als.acquirer_avs_response_code END,
       CASE WHEN als.cvv2_response_code = '' THEN 'Uk' ELSE als.cvv2_response_code END,
       CASE WHEN als.division_name = '' THEN 'Unknown' ELSE als.Division_name END,
       CASE WHEN tt.CLE_Desc = '' OR tt.CLE_Desc = NULL THEN 'X' ELSE tt.Transaction_Type END,
       CASE WHEN als.ISSUING_COUNTRY = '' THEN 'XX' ELSE als.ISSUING_COUNTRY END,
       'X'
FROM Pulse_Test.dbo.Staging_AuthLogSummary_BIN als
     LEFT OUTER JOIN Transaction_Type tt ON als.MOTO_INDICATOR = tt.CLE_Desc
WHERE als.Txn_type IN ('Return', 'Purchase')
  AND als.File_id = @fileid
  AND ACQUIRER = 'Fiserv'
GROUP BY als.DIVISION,
         als.DIVISION_NAME,
         als.DATE_AUTH,
         als.MOTO_INDICATOR,
         als.TXN_TYPE,
         als.ACQUIRER_AVS_RESPONSE_CODE,
         als.CVV2_RESPONSE_CODE,
         als.ISSUING_COUNTRY,
         als.AUTH_COUNT,
         als.AUTH_AMOUNT,
         als.ENTITY_TYPE,
         als.MOP_CODE,
         als.ACQUIRER_AUTH_RESPONSE_CODE,
         als.CARD_TYPE;
t2a7ltrp

t2a7ltrp1#

在查询中,第22行:

CASE WHEN tt.CLE_Desc = '' OR tt.CLE_Desc = NULL THEN 'X' ELSE tt.Transaction_Type END,

在CASE语句的ELSE分支中包含列“tt.Transaction_Type”,并且不涉及GROUP BY子句
重写查询以将此列添加到GROUP BY子句中。
一般通过点击错误信息,光标将被放置在错误的行上,或刚好在...

相关问题