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?

Answer 1

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.