Syntax Errors With Case Statement in SQL

Question

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

If this happened to me, the first thing I would do is count my brackets. By the way, what does the error message say? — Dan Bracuk
– Dan Bracuk, Commented Nov 6, 2013 at 17:40

Indi · Accepted Answer · 2013-11-06 17:42:46Z

3

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

answered Nov 6, 2013 at 17:42

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bryan Over a year ago

Perfect, I was just coming back here because I figured out that taking out the input expression after Case would allow me to search over more tables, but you explained it better than I.

Kobi · Accepted Answer · 2013-11-06 17:45:08Z

0

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' ...

answered Nov 6, 2013 at 17:45

Kobi

2,52418 silver badges31 bronze badges

Collectives™ on Stack Overflow

Syntax Errors With Case Statement in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related