What are the challenges you face when working across database platforms? Take the survey
Options

OR at the end of a line in WHERE clause followed by comment causing script error

danielsilldanielsill Posts: 10 New member
When generating a change script the script generated is invalid when a WHERE statement with OR conditions followed by inline comments is encountered.

This is the WHERE clause in the stored procedure which is also shown in the comparison view in SQL Compare:
WHERE L.L_ServiceStartDate BETWEEN A.PriorVisitDate AND A.PairDate	
	AND H.InactiveFlag IS NULL	
	AND (C.CptHcpcsHippsCode IN ('99221', '99222') OR -- exclude if IP/ER hospital visit
		(C.M_GrpVisitID IN (45,70,26,72,76) -- lab and radiology
	AND C.CptHcpcsHippsCode NOT IN ('72275','75989'))) -- exclude radiology guidance codes
;
<br>

This is resulting WHERE clause in the change script:
WHERE L.L_ServiceStartDate BETWEEN A.PriorVisitDate AND A.PairDate
	AND H.InactiveFlag IS NULL
	AND (C.CptHcpcsHippsCode IN ('99221', '99222')-- exclude if IP/ER hospital visit -- exclude if IP/ER hospital visitOR -- exclude if IP/ER hospital visit
		(C.M_GrpVisitID IN (45,70,26,72,76) -- lab and radiology
	AND C.CptHcpcsHippsCode NOT IN ('72275','75989'))) -- exclude radiology guidance codes<br>


The change script seems to be duplicating the comment at the end of the line 3 multiple times and is then putting the OR behind the comment (appended to the word visit) which results in an error. This is encountered in v.13.6.6.8387 but does not occur in older versions of SQL Compare.


Tagged:

Answers

Sign In or Register to comment.