'Incorrect Syntax near WHERE error in generated SQL

I am generating the below SQL. From my code I am using a where condition list to collect all the Where logic and insert it after the Join logic is set-up. However, I am getting a very generic syntax error and I can't figure out why. I am pretty sure the logic is properly organized however, when inserting the where statement it throws the syntax error

Incorrect Syntax near WHERE

The {h-schema} are just generated database and table names.

The code:

SELECT count(*) AS ID
FROM (
SELECT 'PREAPPROVAL' AS type, pa.id AS id FROM {h-schema}preapproval AS pa 
LEFT JOIN {h-schema}risk_limit AS lim ON pa.limit_id = lim.id 
LEFT JOIN {h-schema}desk AS d ON lim.desk_enterprise_id = d.enterprise_id AND CAST(pa.creation_date_time AS date) BETWEEN d.start_date AND d.end_date

WHERE pa.status = 'APPROVED' AND pd.end_date = NULL <-------------------------SYNTAX ERR HERE
OR pa.status = 'DECLINED' AND pa.completion_date_time > '2021-01-28 13:02:13' 
OR pa.status = 'IN_PROGRESS' 
OR pa.creation_date_time > '2021-01-28 13:02:13'

AND COALESCE(lim.policy_enterprise_id, d.policy_enterprise_id) IN (6)
UNION
SELECT 'BREACH' AS type, br.id AS id FROM {h-schema}breach AS br 
LEFT JOIN {h-schema}risk_limit AS lim ON br.limit_id = lim.id 
LEFT JOIN {h-schema}desk AS d ON lim.desk_enterprise_id = d.enterprise_id 
AND br.reporting_date BETWEEN d.start_date AND d.end_date 
LEFT JOIN {h-schema}valid_breach_recommendation AS vbr_approve ON vbr_approve.id = (SELECT TOP(1) id FROM {h-schema}valid_breach_recommendation 
WHERE breach_id = br.id AND outcome = 'APPROVE' 
ORDER BY creation_date_time DESC, id DESC) 
LEFT JOIN {h-schema}valid_breach_decision AS vbd 
ON vbd.id = (SELECT TOP(1) id FROM {h-schema}valid_breach_decision 
WHERE breach_id = br.id 
ORDER BY creation_date_time DESC, id DESC) 
LEFT JOIN {h-schema}breach AS child_br ON br.id = child_br.parent_breach_id 

WHERE br.status = 'APPROVED' AND vbd.end_date = NULL      <--------------------SYNTAX ERR HERE
OR br.status = 'DECLINED' AND br.completion_date_time > '2021-01-28 13:02:14' 
OR br.status = 'IN_PROGRESS' 
OR br.creation_date_time > '2021-01-28 13:02:14' 

AND child_br.id IS NULL 
AND CASE br.status 
WHEN 'IN_PROGRESS' THEN vbr_approve.start_date 
WHEN 'APPROVED' THEN vbd.start_date 
WHEN 'CANCELLED' THEN vbd.start_date 
ELSE NULL 
END IS NOT NULL AND COALESCE(lim.policy_enterprise_id, d.policy_enterprise_id) IN (6)
) AS issue
javasql-server


Solution 1:[1]

This is actually version issue. In latest version of SQL support like create procedure sp_name ( @name varchar(50) NULL ) ...

But the older version of SQL doesn't support with this way. For older version we need to provide '=NULL'

Solution 2:[2]

There is some syntax error due to {h-schema}, remove {h-schema} and your code has no syntax error. Remove {h-schema} and your code works fine.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 H. Rashed
Solution 2

Related Questions