Working to convert the following statement to T-SQL from PL/SQL (Work in progress). However I've run into an error in SQL Server.
尝试将以下语句从PL / SQL转换为T-SQL（正在进行中）。但是我在SQL Server中遇到了错误。
SELECT COUNT(*) OVER() AS RW_CNT
,COUNT(*) OVER( PARTITION BY(P.PLACEMENT_HISTORY_ID)) AS DP_CNT
,P.PLACEMENT_HISTORY_ID AS DD_PLCMT_HIST_ID
,U.ES_PARTICIPANT_KEY
,A.ES_PARTICIPANT_ATTR_KEY
,ISNULL(CENTER.ES_ORG_KEY, 0) AS ES_CENTER_ORG_KEY
,ISNULL(O.ES_ORG_KEY, 0) AS ES_LOCAL_UNIT_KEY
,ISNULL(STK.ES_ORG_KEY, 0) AS ES_STAKE_UNIT_KEY
,ISNULL(PED.ES_EMPLOYER_KEY, 0) AS ES_PLCMT_EMPLOYER_KEY
,ISNULL(PD.ES_PARTICIPANT_KEY, 0) AS ES_CREATOR_USERS_KEY
,CONVERT(VARCHAR(8), CONVERT(DATE,P.PLACED_DATE), 112) AS PLACED_DATE_KEY
,CONVERT(VARCHAR(8), CONVERT(DATE,P.CREATED_DATE), 112) AS PLCMT_RECORDED_DATE_KEY
,CONVERT(DATETIME, CONVERT(DATE,GETDATE())) AS LOAD_DATE
,1 AS PLCMT_CNT
,CASE WHEN P.PLACEMENT_TYPE = 0 THEN 1 ELSE 0 END AS OBTAIN_JOB_CNT
,CASE WHEN P.PLACEMENT_TYPE = 2 THEN 1 ELSE 0 END AS ENROLL_SBA_CNT
,CASE WHEN P.PLACEMENT_TYPE = 3 THEN 1 ELSE 0 END AS STARTED_BUSINESS_CNT
,CASE WHEN P.PLACEMENT_TYPE = 1 THEN 1 ELSE 0 END AS BEGIN_EDU_TRAINING_CNT
,U.MEMBER_1_0_FLAG AS MEMBER_CNT
,CASE WHEN P.DI_FUNDED = 'Y' THEN 1 ELSE 0 END AS DI_FUNDED_CNT
,ISNULL(DTP.DAYS_TO_PLCMT, PF.DAYS_TO_PLCMT) AS DAYS_TO_PLCMT
,ISNULL(LD.LOCAL_COUNCIL_KEY, isnull(OVERRIDE_LC.Local_Council_Key, 1)) AS LOCAL_COUNCIL_KEY
FROM DSS_ERS_STAGE.ES_W_PLCMT_HIST P
LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_DIM U ON (P.CANDIDATE_ID =
U.CANDIDATE_ID)
LEFT JOIN DSS_ERS_STAGE.ES_W_USER WU ON (U.USER_ID = WU.USER_ID)
LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM O ON (WU.HOME_UNIT_NUMBER =
O.UNIT_NUMBER AND O.ORG_TYPE_ID IN (7,8) AND O.ORG_STATUS_CODE = 1)
LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM STK ON (O.STK_DIST_ORG_ID = STK.ORG_ID
AND STK.ORG_TYPE_ID IN (5,6) AND STK.ORG_STATUS_CODE = 1)
LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM CENTER ON (O.RSC_CNTR_ORG_ID =
CENTER.ORG_ID AND (CENTER.ORG_TYPE_ID IN (60,61)) AND
(CENTER.ORG_STATUS_CODE =1))
LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_ATTR_DIM A ON (A.ID_AS_VARCHAR =
(ISNULL(U.PROF_VISIBLE_1_0_FLAG, '') +
ISNULL(U.PROF_STAFF_1_0_FLAG, '') +
ISNULL(U.DI_ASSOCIATE_1_0_FLAG, '') +
ISNULL(U.PEF_STUDENT_1_0_FLAG, '') +
ISNULL(0, '') +
ISNULL(U.BISHOP_REPORT_1_0_FLAG, '') +
ISNULL(U.PROFILE_ACTIVE_1_0_FLAG, '') +
ISNULL(U.MEMBER_1_0_FLAG, '') +
ISNULL(U.ACCELERATED_1_0_FLAG, '') +
ISNULL(CASE WHEN P.DI_FUNDED = 'Y' THEN
1 ELSE 0 END, '')))
LEFT JOIN DSS_ERS_STAGE.ES_PLCMT_EMPLOYER_DIM PED ON
(P.PLACEMENT_HISTORY_ID = PED.CAND_HIST_PLCMT_ID)
LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_DIM PD ON (P.CREATED_BY_USER =
PD.USER_ID)
LEFT JOIN DSS_ERS_STAGE.ES_W_DTP_MAPPING_SS DTP ON (P.PLACEMENT_HISTORY_ID =
DTP.PLACEMENT_HISTORY_ID)
LEFT JOIN DSS_ERS_STAGE.ES_PLACEMENT_FACT PF ON (P.PLACEMENT_HISTORY_ID =
PF.DD_PLCMT_HIST_ID)
LEFT JOIN DSS_ERS_STAGE.ES_LOCAL_COUNCIL_DIM LD ON (P.LOCAL_COUNCIL =
LD.LOCAL_COUNCIL_ID)
LEFT JOIN DSS_ERS_STAGE.ES_LOCAL_COUNCIL_DIM OVERRIDE_LC ON
(STK.LOCAL_COUNCIL = OVERRIDE_LC.LOCAL_COUNCIL_NAME)
--WHERE P.PLACEMENT_HISTORY_ID = 1215
GROUP BY
P.PLACEMENT_HISTORY_ID
,U.ES_PARTICIPANT_KEY
,A.ES_PARTICIPANT_ATTR_KEY
,CENTER.ES_ORG_KEY
,O.ES_ORG_KEY
,STK.ES_ORG_KEY
,CONVERT(VARCHAR(8), CONVERT(DATE,P.PLACED_DATE), 112)
,CONVERT(VARCHAR(8), CONVERT(DATE,P.CREATED_DATE), 112)
,CONVERT(DATETIME, CONVERT(DATE,GETDATE()))
,CASE WHEN P.PLACEMENT_TYPE = 0 THEN 1 ELSE 0 END
,CASE WHEN P.PLACEMENT_TYPE = 2 THEN 1 ELSE 0 END
,CASE WHEN P.PLACEMENT_TYPE = 3 THEN 1 ELSE 0 END
,CASE WHEN P.PLACEMENT_TYPE = 1 THEN 1 ELSE 0 END
,U.MEMBER_1_0_FLAG
,CASE WHEN P.DI_FUNDED = 'Y' THEN 1 ELSE 0 END
,PED.ES_EMPLOYER_KEY
,PD.ES_PARTICIPANT_KEY
,ISNULL(DTP.DAYS_TO_PLCMT, PF.DAYS_TO_PLCMT)
,ISNULL(LD.LOCAL_COUNCIL_KEY, isnull(OVERRIDE_LC.Local_Council_Key, 1))
I'm getting this error in SQL Server. It's fairly ambiguous. The resources I've found on the web haven't helped much in understanding what the issue is here.
我在SQL Server中收到此错误。这很模糊。我在网上找到的资源对于理解这里的问题没有多大帮助。
Msg 164, Level 15, State 1, Line 55
Each GROUP BY expression must contain at least one column that is not an
outer reference.
*I'm adding this sentence because * is telling me that my post is mostly code and that I need more details......
*我正在添加这句话，因为*告诉我我的帖子主要是代码，我需要更多细节......
1 个解决方案
#1
3
You get this error when you have a constant as a key in the GROUP BY. For some reason, that bothers SQL Server.
如果在GROUP BY中有一个常量作为键，则会出现此错误。出于某种原因，这困扰着SQL Server。
In your case, the "constant" appears to be:
在您的情况下，“常数”似乎是：
CONVERT(DATETIME, CONVERT(DATE, GETDATE()))
Simply remove this from the GROUP BY keys.
只需从GROUP BY键中删除它即可。
(And, just for the record, each reference to GETDATE() is evaluated once per query, so it is a "constant" for this purpose.)
（并且，只是为了记录，每个查询对每个GETDATE（）的引用进行一次评估，因此它是一个“常量”。）
#1
3
You get this error when you have a constant as a key in the GROUP BY. For some reason, that bothers SQL Server.
如果在GROUP BY中有一个常量作为键，则会出现此错误。出于某种原因，这困扰着SQL Server。
In your case, the "constant" appears to be:
在您的情况下，“常数”似乎是：
CONVERT(DATETIME, CONVERT(DATE, GETDATE()))
Simply remove this from the GROUP BY keys.
只需从GROUP BY键中删除它即可。
(And, just for the record, each reference to GETDATE() is evaluated once per query, so it is a "constant" for this purpose.)
（并且，只是为了记录，每个查询对每个GETDATE（）的引用进行一次评估，因此它是一个“常量”。）