A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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) ;
This looks dubious:
You really want a string that reads NULL rather than a NULL value?
Try this fix: ...Insert Into ##Test2 values (....