sql - PL/SQL 到 T-SQL 转换错误
问题描述
正在努力将以下语句从 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))
我在 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.
*我添加这句话是因为stackoverflow告诉我我的帖子主要是代码,我需要更多细节......
解决方案
当您有一个常量作为GROUP BY
. 出于某种原因,这困扰着 SQL Server。
在您的情况下,“常数”似乎是:
CONVERT(DATETIME, CONVERT(DATE, GETDATE()))
只需从GROUP BY
键中删除它即可。
(并且,为了记录,每个对每个查询的引用都GETDATE()
被评估一次,因此它是一个“常量”用于此目的。)
推荐阅读
- jupyter-notebook - 为多个 ipywidgets 创建一个观察处理程序
- python - 损失函数中的 Keras batch_dot 维度问题
- javascript - 检测丢失的字符字形,以及 Firefox 的带有十六进制代码点的小盒子
- javascript - 在较小的显示器上使图像不以全分辨率加载
- python - 如何在 python (Pycharm) 中读取 Hjson 文件?
- python-3.x - 从熊猫添加数据时如何为谷歌工作表动态设置“范围”
- python - __future__ 语句如何知道新 Python 版本的语法?
- linux - Docker 端口映射是主机到容器还是容器到主机?
- php - 类 stdClass 的对象无法转换为字符串(Laravel 查询生成器)
- python - 风格转移:在 tensorflow 1.15.0 中保存和恢复检查点/模型