当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - GROUP BY expression must contain at least... UNION Query

问题描述:

I have a large query that is actually a view and it worked in Oracle but doesn't in MS-SQL Server. The second half of the UNION query intentionally will have hard coded values.

I'm posting the original query that worked in Oracle 11g just fine although I tried another method that didn't have the two hard-coded values of 0, 'Financials'. That attempt is posted at the bottom.

The error I receive is: Each GROUP BY expression must contain at least one column that is not an outer reference.

 SELECT

RECOVERYTXNLEDGER.TXN_ID AS TXN_ID,

RECOVERYTXNLEDGER.REC_PROC_ID AS RECORD_PROC_ID,

RECOVERYPROCESS.REC_PROC_NAME AS RECORD_PROC_NAME,

RECOVERYPROCESS.REC_PROC_TYPE_ID AS RECORD_PROC_TYPE_ID,

RECOVERYPROCTYPE.REC_PROC_TYPE_NAME AS RECORD_PROC_TYPE_NAME,

RECOVERYPROCESS.REC_PROC_SEQ AS RECORD_PROC_SEQUENCE_NUM,

RECOVERYTXNLEDGER.SVC_ID AS SERVICE_ID,

SERVICECATALOG.SVC_REF AS SERVICE_REFERENCE,

SERVICECATALOG.SVC_DESC AS SERVICE_DESCRIPTION,

SERVICECATALOG.SDG_ID AS SERVICE_STUDY_GROUP_ID,

STUDYGROUP1.SDG_REF AS SERVICE_STUDY_GROUP_REF,

STUDYGROUP1.SDG_DESC AS SERVICE_STUDY_GROUP_DESC,

RECOVERYTXNLEDGER.SVC_PER_ID AS SERVICE_PERIOD_ID,

PERIOD1.PER_REF AS SERVICE_PERIOD_REFERENCE,

PERIOD1.PER_DESC AS SERVICE_PERIOD_DESCRIPT,

RECOVERYTXNLEDGER.SVC_SCN_ID AS SERVICE_SCENARIO_ID,

SCENARIO1.SCN_DESC AS SERVICE_SCENARIO_DESC,

RECOVERYTXNLEDGER.SVC_RATE AS SERVICE_RATE,

RECOVERYTXNLEDGER.REC_TRANS_TYPE_ID AS TRANS_TYPE_ID,

RECOVERYTRANSTYPE.REC_TRANS_TYPE_NAME AS TRANS_TYPE_NAME,

RECOVERYTXNLEDGER.CUST_ACCT_REF AS CUSToMER_ACCOUNT,

RECOVERYTXNLEDGER.ORG_REF AS ORGANIZATION_REFERENCE,

ORGANIZATION.ORG_DESC AS ORGANIZATION_DESCRIPTION,

STUDYGROUP.SDG_ID AS STUDY_GROUP_ID,

STUDYGROUP.SDG_REF AS STUDY_GROUP_REFERENCE,

STUDYGROUP.SDG_DESC AS STUDY_GROUP_DESCRIPT,

SUM(RECOVERYTXNLEDGER.REC_AMT) AS TRANSACTION_AMOUNT,

RECOVERYTXNLEDGER.VOL_ID AS VOLUME_ID,

VOLUMECATALOG.VOL_REF AS VOLUME_REFERENCE,

VOLUMECATALOG.VOL_DESC AS VOLUME_DESCRIPTION,

RECOVERYTXNLEDGER.VOL_PER_ID AS VOLUME_PERIOD_ID,

PERIOD.PER_REF AS VOLUME_PERIOD_REFERENCE,

PERIOD.PER_DESC AS VOLUME_PERIOD_DESCRIPT,

RECOVERYTXNLEDGER.VOL_SCN_ID AS VOLUME_SCENARIO_ID,

SCENARIO.SCN_DESC AS VOLUME_SCENARIO_DESCRIPT,

RECOVERYTXNLEDGER.VOL_QTY AS VOLUME_QUANTITY

FROM RECOVERYTXNLEDGER

INNER JOIN SCENARIO SCENARIO1 ON RECOVERYTXNLEDGER.SVC_SCN_ID = SCENARIO1.SCN_ID

INNER JOIN SERVICECATALOG ON RECOVERYTXNLEDGER.SVC_ID = SERVICECATALOG.SVC_ID

INNER JOIN STUDYGROUP STUDYGROUP1 ON SERVICECATALOG.SDG_ID = STUDYGROUP1.SDG_ID

INNER JOIN PERIOD PERIOD1 ON RECOVERYTXNLEDGER.SVC_PER_ID = PERIOD1.PER_ID

INNER JOIN RECOVERYPROCESS ON RECOVERYTXNLEDGER.REC_PROC_ID = RECOVERYPROCESS.REC_PROC_ID

INNER JOIN RECOVERYPROCTYPE ON RECOVERYPROCESS.REC_PROC_TYPE_ID = RECOVERYPROCTYPE.REC_PROC_TYPE_ID

INNER JOIN ORGANIZATION ON RECOVERYTXNLEDGER.ORG_REF = ORGANIZATION.ORG_REF

INNER JOIN STUDYGROUP ON ORGANIZATION.SDG_ID = STUDYGROUP.SDG_ID

INNER JOIN RECOVERYTRANSTYPE ON RECOVERYTXNLEDGER.REC_TRANS_TYPE_ID = RECOVERYTRANSTYPE.REC_TRANS_TYPE_ID

INNER JOIN VOLUMECATALOG ON RECOVERYTXNLEDGER.VOL_ID = VOLUMECATALOG.VOL_ID

INNER JOIN PERIOD ON RECOVERYTXNLEDGER.VOL_PER_ID = PERIOD.PER_ID

INNER JOIN SCENARIO ON RECOVERYTXNLEDGER.VOL_SCN_ID = SCENARIO.SCN_ID

GROUP BY

RECOVERYTXNLEDGER.REC_PROC_ID,

RECOVERYTXNLEDGER.TXN_ID,

RECOVERYPROCESS.REC_PROC_NAME,

RECOVERYPROCESS.REC_PROC_TYPE_ID,

RECOVERYPROCTYPE.REC_PROC_TYPE_NAME,

RECOVERYPROCESS.REC_PROC_SEQ,

RECOVERYTXNLEDGER.SVC_ID,

SERVICECATALOG.SVC_REF,

SERVICECATALOG.SVC_DESC,

SERVICECATALOG.SDG_ID,

STUDYGROUP1.SDG_REF,

STUDYGROUP1.SDG_DESC,

RECOVERYTXNLEDGER.SVC_PER_ID,

PERIOD1.PER_REF,

PERIOD1.PER_DESC,

RECOVERYTXNLEDGER.SVC_SCN_ID,

SCENARIO1.SCN_DESC,

RECOVERYTXNLEDGER.SVC_RATE,

RECOVERYTXNLEDGER.REC_TRANS_TYPE_ID,

RECOVERYTRANSTYPE.REC_TRANS_TYPE_NAME,

RECOVERYTXNLEDGER.CUST_ACCT_REF,

RECOVERYTXNLEDGER.ORG_REF,

ORGANIZATION.ORG_DESC,

STUDYGROUP.SDG_ID,

STUDYGROUP.SDG_REF,

STUDYGROUP.SDG_DESC,

RECOVERYTXNLEDGER.VOL_ID,

VOLUMECATALOG.VOL_REF,

VOLUMECATALOG.VOL_DESC,

RECOVERYTXNLEDGER.VOL_PER_ID,

PERIOD.PER_REF,

PERIOD.PER_DESC,

RECOVERYTXNLEDGER.VOL_SCN_ID,

SCENARIO.SCN_DESC,

RECOVERYTXNLEDGER.VOL_QTY

UNION

SELECT

NULL AS TXN_ID,

NULL AS REC_PROC_ID,

NULL AS RECORD_PROC_NAME,

NULL AS RECORD_PROC_TYPE_ID,

NULL AS RECORD_PROC_TYPE_NAME,

NULL AS RECORD_PROC_SEQUENCE_NUM,

NULL AS SERVICE_ID,

NULL AS SERVICE_REFERENCE,

NULL AS SERVICE_DESCRIPTION,

STUDYGROUP.SDG_ID AS SERVICE_STUDY_GROUP_ID,

STUDYGROUP.SDG_REF AS SERVICE_STUDY_GROUP_REF,

STUDYGROUP.SDG_DESC AS SERVICE_STUDY_GROUP_DESC,

NULL AS SERVICE_PERIOD_ID,

NULL AS SERVICE_PERIOD_REFERENCE,

NULL AS SERVICE_PERIOD_DESCRIPT,

NULL AS SERVICE_SCENARIO_ID,

NULL AS SERVICE_SCENARIO_DESC,

NULL AS SERVICE_RATE,

0 AS TRANS_TYPE_ID,

'Financials' AS TRANS_TYPE_NAME,

NULL AS CUSTOMER_ACCOUNT,

Financials.ORG_REF AS ORGANIZATION_REFERENCE,

Organization.ORG_DESC AS ORGANIZATION_DESCRIPTION,

StudyGroup.SDG_ID AS STUDY_GROUP_ID,

StudyGroup.SDG_REF AS STUDY_GROUP_REFERENCE,

StudyGroup.SDG_DESC AS STUDY_GROUP_DESCRIPT,

Sum(Financials.GL_AMOUNT) AS TRANSACTION_AMOUNT,

Financials.FIN_ID AS VOLUME_ID,

FINANCIALS.GLACCT_REF AS VOLUME_REFERENCE,

FINANCIALS.GLACCT_DESC AS VOLUME_DESCRIPTION,

Financials.FIN_PER_ID AS VOLUME_PERIOD_ID,

Period.PER_REF AS VOLUME_PERIOD_REFERENCE,

Period.PER_DESC AS VOLUME_PERIOD_DESCRIPT,

Financials.FIN_SCN_ID AS VOLUME_SCENARIO_ID,

Scenario.SCN_DESC AS VOLUME_SCENARIO_DESCRIPT,

NULL AS VOLUME_QUANTITY

FROM (((StudyGroup INNER JOIN Organization ON StudyGroup.SDG_ID = Organization.SDG_ID)

INNER JOIN Financials ON Organization.ORG_REF = Financials.ORG_REF)

INNER JOIN Scenario ON Financials.FIN_SCN_ID = Scenario.SCN_ID)

INNER JOIN Period ON Financials.FIN_PER_ID = Period.PER_ID

GROUP BY

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

STUDYGROUP.SDG_ID,

STUDYGROUP.SDG_REF,

STUDYGROUP.SDG_DESC,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

0,

'Financials',

NULL,

FINANCIALS.ORG_REF,

ORGANIZATION.ORG_DESC,

STUDYGROUP.SDG_ID,

STUDYGROUP.SDG_REF,

STUDYGROUP.SDG_DESC,

FINANCIALS.FIN_ID,

FINANCIALS.GLACCT_REF,

FINANCIALS.GLACCT_DESC,

FINANCIALS.FIN_PER_ID,

PERIOD.PER_REF,

PERIOD.PER_DESC,

FINANCIALS.FIN_SCN_ID,

SCENARIO.SCN_DESC,

NULL

I did try replacing the two literals with:

CASE WHEN StudyGroup.SDG_ID >= 0 THEN 0 ELSE 0 END AS TRANS_TYPE_ID,

CASE WHEN StudyGroup.SDG_ID >= 0 THEN 'Financials' ELSE 'Financials' END AS TRANS_TYPE_NAME,

and in the GROUP BY:

CASE WHEN StudyGroup.SDG_ID >= 0 THEN 0 ELSE 0 END,

CASE WHEN StudyGroup.SDG_ID >= 0 THEN 'Financials' ELSE 'Financials' END,

Thanks...

网友答案:

I would recommend using table aliases especially with larger queries like this. It makes reading it a lot easier to see what is going on. I tweaked your second query a bit and added some aliases. I then realized you have the same 3 columns in your query twice and you were grouping by them multiple times. This won't affect the outcome but it is a bit less confusing. I am guessing that the problem is in your second query so I would isolate that one and get it working before you add it to the UNION.

SELECT 
   NULL                        AS TXN_ID,
   NULL                        AS REC_PROC_ID,
   NULL                        AS RECORD_PROC_NAME,
   NULL                        AS RECORD_PROC_TYPE_ID,
   NULL                        AS RECORD_PROC_TYPE_NAME,
   NULL                        AS RECORD_PROC_SEQUENCE_NUM,
   NULL                        AS SERVICE_ID,
   NULL                        AS SERVICE_REFERENCE,
   NULL                        AS SERVICE_DESCRIPTION,
   sg.SDG_ID           AS SERVICE_STUDY_GROUP_ID,
   sg.SDG_REF          AS SERVICE_STUDY_GROUP_REF,
   sg.SDG_DESC         AS SERVICE_STUDY_GROUP_DESC,
   NULL                        AS SERVICE_PERIOD_ID,
   NULL                        AS SERVICE_PERIOD_REFERENCE,
   NULL                        AS SERVICE_PERIOD_DESCRIPT,
   NULL                        AS SERVICE_SCENARIO_ID,
   NULL                        AS SERVICE_SCENARIO_DESC,
   NULL                        AS SERVICE_RATE,
   0                           AS TRANS_TYPE_ID, 
   'Financials'                AS TRANS_TYPE_NAME, 
   NULL                        AS CUSTOMER_ACCOUNT,
   f.ORG_REF          AS ORGANIZATION_REFERENCE, 
   o.ORG_DESC       AS ORGANIZATION_DESCRIPTION, 
   sg.SDG_ID           AS STUDY_GROUP_ID, 
   sg.SDG_REF          AS STUDY_GROUP_REFERENCE, 
   sg.SDG_DESC         AS STUDY_GROUP_DESCRIPT, 
   Sum(f.GL_AMOUNT)   AS TRANSACTION_AMOUNT, 
   f.FIN_ID           AS VOLUME_ID,
   f.GLACCT_REF       AS VOLUME_REFERENCE,
   f.GLACCT_DESC      AS VOLUME_DESCRIPTION,
   f.FIN_PER_ID       AS VOLUME_PERIOD_ID, 
   p.PER_REF              AS VOLUME_PERIOD_REFERENCE, 
   p.PER_DESC             AS VOLUME_PERIOD_DESCRIPT, 
   f.FIN_SCN_ID       AS VOLUME_SCENARIO_ID, 
   s.SCN_DESC           AS VOLUME_SCENARIO_DESCRIPT,
   NULL                        AS VOLUME_QUANTITY
   FROM StudyGroup sg 
   INNER JOIN Organization o ON sg.SDG_ID = o.SDG_ID
   INNER JOIN Financials f ON o.ORG_REF = f.ORG_REF
   INNER JOIN Scenario s ON f.FIN_SCN_ID = s.SCN_ID
   INNER JOIN Period p ON f.FIN_PER_ID = p.PER_ID
   GROUP BY 
   sg.SDG_ID,
   sg.SDG_REF,
   sg.SDG_DESC,
   f.ORG_REF,
   o.ORG_DESC,
   f.FIN_ID,
   f.GLACCT_REF,
   f.GLACCT_DESC,
   f.FIN_PER_ID,
   p.PER_REF,
   p.PER_DESC,
   f.FIN_SCN_ID,
   s.SCN_DESC

Also, I am not sure you are using UNION because you want to eliminate duplicates between the two queries or not. If not you will get a little better performance by using UNION ALL so it doesn't have to check for duplicates.

分享给朋友:
您可能感兴趣的文章:
随机阅读: