samedi 28 mars 2020

Strange issue combining IF and CASE in TSQL query

Here is an issue that seems like it should be a simple solve but I have been working on it some time and cannot figure out why I cannot seem to combine CASE in one area of the query and IF in another.

Does anyone see what is going on here? I have an old data set that needs to be converted to work with new tables. The data is pulled from WDDX and put in a temp table. That's all working properly.

Here is where I am running into trouble. When it's simply CASE, everything works fine, but when I add IF to grab a value, it tells me:

"Incorrect syntax near the keyword 'IF'. Msg 156, Level 15, State 1, Procedure OldSysDataConv Incorrect syntax near the keyword 'THEN'.

Code is:

 SELECT VarName, 
 CASE 
   WHEN (CHARINDEX('XXXX', VarName) > 0 and SUBSTRING(VarName, CHARINDEX('XXXX', VarName), len(VarName)) like '%XXXX%') or SUBSTRING(VarName, CHARINDEX('ZZZZ', VarName), len(VarName)) like '%ZZZZ%' then

    left(replace(left(replace(VarName, 'XXXX', ''), len(VarName)-4), 'ZZZZ', ''), (len(VarName)-5))
else
    null
END as DetailName,
IF CHARINDEX('ZZZZ', VarName) > 0 THEN
    right(VarName, 1)
END
 as DetailValue,

Aucun commentaire:

Enregistrer un commentaire