Subquery: SQL Error [156] [S0001]: Incorrect syntax near the keyword 'WHERE'

Question

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?

My expectation is to display the data which row numbers > 10, if replaced with ON it still displays 100 data — My August, Apr 17 '23 at 13:23
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Apr 17 '23 at 13:24
why not just use the SELECT TOP 10 with some offset if that's what you want — siggemannen, Apr 17 '23 at 13:24

Vladimir Baranov · Accepted Answer · 2023-04-17T13:34:56.660

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.

My expectation is to display the data which row numbers > 10, if replaced with ON it still displays 100 data — My August, Apr 17 '23 at 13:23
Because you misunderstand what your subquery does, @MyAugust . The reference to `[HIS].Patient.Patient` in your subquery is a separate "instance" to the one you reference in your outer query. — Thom A, Apr 17 '23 at 13:24

Subquery: SQL Error [156] [S0001]: Incorrect syntax near the keyword 'WHERE'

1 Answers1