error "incorrect syntax near 'case'. expecting ".", id, pseudocol, or quoted_id

Anna 40 Reputation points
2023-05-05T11:52:12.33+00:00

I am trying to write a script with a 'Case When' statement and I keep getting the error "Incorrect syntax near the keyword 'CASE'. The statement is copied from a stored procedure and runs fine there. If I comment out the first instance, I get the same error on the next instance so whatever I've done wrong, I did it alot. I've included the first part of the query with the related from files.

With CTE (ReferralID, CaseFileIdentifier, NoteText, NoteTypeOther, RowNote)
AS (
	SELECT ReferralId, CaseFileIdentifier, NoteText, NoteTypeOther,ROW_NUMBER () OVER(PARTITION BY CaseFileIdentifier ORDER BY ContactedOn ASC)
	FROM ReportsLODN.dbo.TC_CaseNotes
	)
Insert Into ##Test2 (
	pt.ID
	, CASE WHEN rd.ReportDate < '1900-01-02' THEN ref.ReferredOn ELSE rd.ReportDate END --AS ReportDate
	, ref.Id --AS ReferralId,
	, ref.ReferredOn --AS ReferralDate,
	, ref.CaseFileIdentifier --AS ReferralNumber,
	, CASE WHEN pt.AgeUnits = 'M' THEN ROUND(CAST(pt.Age AS float)/12, 2)
		WHEN pt.AgeUnits = 'D' THEN ROUND(CAST(pt.Age AS float)/365, 2)
		WHEN pt.AgeUnits = 'W' THEN ROUND(CAST(pt.Age AS float)/52, 2)
		ELSE pt.Age 
	  END --AS Age,
	, pt.Sex --AS Gender,
/*lots of other lines including other CASE WHEN statements*/
	, CASE WHEN CTE.NoteTypeOther LIKE '%revocation%'  
		THEN WHEN CTE.RowNote = 1
		THEN CTE.NoteText
		ELSE 'NULL' 
	  END END --AS RevocationNote
)
FROM student_info pt 
	LEFT JOIN student_referral ref ON pt.Id = ref.PatientId
	LEFT JOIN CTE ON CTE.CaseFileIdentifier = ref.CaseFileIdentifier                           --pt.Id = ref.PatientId
	LEFT OUTER JOIN ReportDate rd ON pt.Id = rd.ID
WHERE ref.CaseFileIdentifier IS NOT NULL
AND rd.ReportDate > '2023-05-02'
and NOT pt.ID IN 
	(SELECT StaffStudentID 
	FROM ClassList)
;
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
{count} votes

0 additional answers

Sort by: Most helpful