英文:
Subquery: SQL Error [156] [S0001]: Incorrect syntax near the keyword 'WHERE'
问题
The error in your SQL query is related to the use of the WHERE
clause in the subquery. In SQL Server, when you use a WHERE
clause in a subquery that is part of a JOIN
, you should use the ON
clause to specify the join condition for that subquery. Here's the corrected query:
SELECT TOP(100)
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at]
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
JOIN
(SELECT
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p
WHERE ROW_NUMBER() > 10) AS TMP
ON TMP.row > 10;
I've moved the WHERE
clause inside the subquery and used the ON
clause to specify the join condition between the subquery TMP
and the other tables. This should resolve the syntax error.
英文:
Query :
SELECT TOP(100)
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at]
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
JOIN
(SELECT
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p) AS TMP
WHERE
TMP.row > 10;
Error in subquery :
(SELECT
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM [HIS].Patient.Patient p) AS TMP
WHERE TMP.row > 10;
> SQL Error [156] [S0001]:
> Incorrect syntax near the keyword 'WHERE'.
I don't know where it's wrong, isn't the syntax I wrote correct?
答案1
得分: 0
The JOIN
needs an ON
part with a logical condition.
如果你用词汇WHERE
替换为ON
,查询将在语法上正确。
尽管如此,它很可能产生你不期望的结果。
One common way to express what you likely need is to use a Common Table Expression (CTE).
一种常见的表达你可能需要的方法是使用公共表达式(CTE)。
WITH CTE
AS
(
SELECT
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at],
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
)
SELECT TOP(100)
[id],
[patient_id],
[local_mr_no],
[organization_code],
[organization_name],
[registration_date],
[status_id],
[status],
[created_at],
[updated_at],
[deleted_at]
FROM CTE
WHERE
row > 10
;
Or, inline without the CTE:
或者,内联方式不使用CTE:
SELECT TOP(100)
[id],
[patient_id],
[local_mr_no],
[organization_code],
[organization_name],
[registration_date],
[status_id],
[status],
[created_at],
[updated_at],
[deleted_at]
FROM
(
SELECT
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at],
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
) AS TMP
WHERE TMP.row > 10
;
不确定TOP(100)
应该放在内部还是外部SELECT
。它可以放在任何一个位置,产生不同的结果。你知道你需要哪个变体。
英文:
The JOIN
needs an ON
part with a logical condition.
If you replace the word WHERE
with the word ON
the query would become syntactically correct.
Though, it would most likely produce not the results you expect.
One common way to express what you likely need is to use a Common Table Expression (CTE).
WITH CTE
AS
(
SELECT
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at],
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
)
SELECT TOP(100)
[id],
[patient_id],
[local_mr_no],
[organization_code],
[organization_name],
[registration_date],
[status_id],
[status],
[created_at],
[updated_at],
[deleted_at]
FROM CTE
WHERE
row > 10
;
Or, inline without the CTE:
SELECT TOP(100)
[id],
[patient_id],
[local_mr_no],
[organization_code],
[organization_name],
[registration_date],
[status_id],
[status],
[created_at],
[updated_at],
[deleted_at]
FROM
(
SELECT
'' AS [id],
p.PatientId AS [patient_id],
po.MrNo AS [local_mr_no],
o.Code AS [organization_code],
o.Name AS [organization_name],
p.RegistrationDate AS [registration_date],
p.PatientStatusId AS [status_id],
ps.Name AS [status],
'' AS [created_at],
'' AS [updated_at],
'' AS [deleted_at],
ROW_NUMBER() OVER(ORDER BY p.PatientId) AS ROW
FROM
[HIS].Patient.Patient p
JOIN
[HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
[HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
[HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
) AS TMP
WHERE TMP.row > 10
;
Not sure whether the TOP(100)
should go into the inner or outer SELECT
. It can go either way and produce different result. You know which variant you need.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论