Subquery: SQL错误 [156] [S0001]: 关键字’WHERE’附近的语法不正确。

huangapple go评论58阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年4月17日 21:08:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76035511.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定