CODEWITHSUNDEEP
sql-server-2005
user2576682
user2576682

Reputation: 123

Incorrect syntax near '=' Error CASE Statement

I have tried to add these case statements ,(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt) as 'WriteOff' to my code I am getting an incorrect sytax near ')' error. Msg 102, Level 15, State 1, Line 18

What I want to happen is if the field adj.adjcat is write_off then put the value from adj.amt in the column WriteOff With the following code:

SELECT 
ar.UCI
,ar.reportmonth
,(case when ar.transagebucket < 1 then '0_30'
    when ar.transagebucket = 1 then '31_60'
    when ar.transagebucket = 2 then '61_90'
    when ar.transagebucket = 3 then '91_120'
    when ar.transagebucket = 4 then '121_150'
    when ar.transagebucket = 5 then '151_180'
    else 'Over180' end) as AgeBucket
,ar.PatName as PatientName
,ar.AcctNu as AccountNumber
,Convert (varchar(10),ar.dos,101) as Svc_Date
,ar.cptdisplay
,(ins.insdesc+ ' - ' +ins.insmne) as Insurance
,Sum(ar.chgamt) as ChgAmt
,ISNULL(Sum(pmt.pmtamt),0) as PmtAmt
,(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt) as 'WriteOff'
,(CASE WHEN adj.adjcat = 'DEBIT' THEN adj.amt) as 'Debit'
,(CASE WHEN adj.adjcat = 'ADMIN_ADJ' THEN adj.amt) as 'AdminAdj'    
,ISNULL((ar.curbal),0) as CurBal
FROM 
rpt_dat_ARDetail ar 
LEFT JOIN rpt_dat_PmtDetail pmt ON ar.clntid = pmt.clntid 
AND ar.AcctNu =    pmt.AcctNu    
and ar.dos = pmt.dos and ar.reportmonth = pmt.reportmonth and ar.chgamt = pmt.chgamt 
and ar.curbal = pmt.curbal 
LEFT JOIN rpt_dat_AdjustmentDetail adj ON ar.clntid = adj.clntid 
AND ar.aid = adj.aid AND ar.dos = adj.dos
INNER JOIN rpt_dic_Ins ins ON ar.insmne = ins.insmne AND ar.clntid = ins.clntid

Upvotes: 0

Views: 2138

Answers (2)

i-one
i-one

Reputation: 5120

There should be

(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt END) as 'WriteOff'

OR

(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt ELSE '' END) as 'WriteOff'

instead of

(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt) as 'WriteOff'

The same for Debit and AdminAdj.

Upvotes: 1

anon
anon

Reputation:

You are missing an END. Also you should avoid 'single quote' aliases and use [square brackets] instead:

(CASE WHEN adj.adjcat = 'WRITE_OFF' THEN adj.amt END) as [WriteOff]

Upvotes: 2

Related Questions