Incorrect Syntax Near "="

jgilmore · June 24th, 2011, 10:55 AM

I'm trying to make dbo.OrgUnits.OrgPath = "NB" when dbo.OrgUnits.OrgID is null. However I get an error message of 'Incorrect Syntax Near "="' with my code (see below). Can someone please tell me what I'm doing wrong.

SELECT dbo.GLAccounts.GLID, dbo.GLAccounts.BaseID, dbo.OrgUnits.OrgID, dbo.OrgUnits.OrgPath, dbo.OrgUnits.OrgLongName,

Case
When dbo.OrgUnits.OrgID=""
Then dbo.OrgUnits.OrgPath="NB"
End

FROM dbo.GLAccounts LEFT OUTER JOIN
dbo.OrgUnits ON dbo.GLAccounts.OrgID = dbo.OrgUnits.OrgID

Jeff Moden · June 25th, 2011, 09:31 PM

You can't use CASE as if it were an IF in any language. It has to be a part of an "assignment". Give the following a try and see if it does what you want.

Code:


　SELECT　dbo.GLAccounts.GLID,　dbo.GLAccounts.BaseID,　dbo.OrgUnits.OrgID,　
　　　　　　　　OrgPath　=　
　　　　　　　　　　　　CASE
　　　　　　　　　　　　WHEN　dbo.OrgUnits.OrgID　=　''
　　　　　　　　　　　　THEN　'NB'
　　　　　　　　　　　　ELSE　dbo.OrgUnits.OrgPath
　　　　　　　　　　　　END
　　　　　　　　dbo.OrgUnits.OrgLongName
　　　FROM　dbo.GLAccounts　
　　　LEFT　OUTER　JOIN　dbo.OrgUnits　
　　　　　ON　dbo.GLAccounts.OrgID　=　dbo.OrgUnits.OrgID
;