CODEWITHSUNDEEP
sqlcasecase-when
Bryan
Bryan

Reputation: 67

Syntax Errors With Case Statement in SQL

If the When conditions are correct, then I want it to be labeled as 'Suspended for Audit....' if they are not correct, then have it be either blank or filled in by the t.fstrTaskSource + 'TYP' + t.fstrType statement (this part works already)

SELECT  t.flngKey AS flngTaskKey,
t.fstrAccountType,
t.fstrTaskSource,
CASE    t.fstrCategory 
    WHEN    '' THEN '' 
    ELSE    t.fstrTaskSource + '_CAT_' + t.fstrCategory 
    END AS fstrCategory,
CASE    t.fstrType 
    WHEN    '' THEN '' 
    WHEN    (wd.fstrWorkType    = 'SUSIN1' -- I am getting a syntax error here on the = sign --
        AND wd.fstrOwner        =  ' ' 
        AND wd.flngworkkey      =  wr.flngworkkey 
        AND wr.fstrAccountType  <> '007' 
        AND wr.fblnOpen         =  1 
        AND EXISTS  
            (SELECT 1 
            FROM    tblIndicator i
            WHERE   i.fstrIndicator   = 'EIWTCH' 
            AND i.flngVer         = 0 
            AND i.flngAccountKey  = wd.flngAccountKey)) -- I am also getting an error here on the ) sign --
    THEN 'Suspended for Audit Indicator - EIC Watch For'
    ELSE    t.fstrTaskSource + '_TYP_' + t.fstrType 
    END AS fstrType

Upvotes: 2

Views: 11653

Answers (2)

Kobi
Kobi

Reputation: 2524

Your are trying to use the two syntax of CASE in the same time

the first:

case expression
    when "val1" then ..
    when "val2" then ..
end

the second:

case
    when column = "val1" then ..
    when column2 = "val2" then ..
end

So, use this in your second CASE:

CASE
    WHEN t.fstrType = '' THEN ''
    WHEN (wd.fstrWorkType = 'SUSIN1' ...

Upvotes: 0

Kaf
Kaf

Reputation: 33849

Your second Case Expression is a mix of Simple Case and Searched Case.

I.e.

CASE    t.fstrType 
  WHEN    '' THEN '' 
  WHEN    (wd.fstrWorkType    = 'SUSIN1'

Change it to a Searched Case expression as:

CASE WHEN t.fstrType = '' THEN ''
     WHEN (wd.fstrWorkType    = 'SUSIN1'  ...

Two formats of Case Expression are: 

--Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 

--Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Upvotes: 3

Related Questions