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

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?

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.