CODEWITHSUNDEEP
sql-servert-sql
deepti
deepti

Reputation: 729

SQL case giving error while using substring

I am facing an error while using a SQL Server CASE statement with a SUBSTRING function call:

DECLARE @string VARCHAR(MAX)

SELECT 
     sys.Netbios_Name0, sys.name0, sys.Operating_System_Name_and0,
     sys.AD_Site_Name0, sys1.DisplayName0, sys1.Version0,
     sys2.TopConsoleUser0, sys3.model0,
     CASE sys.Operating_System_Name_and0
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '6.3' 
           THEN 'Windows 8' 
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '6.2' 
           THEN 'Windows 8' 
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '6.1'  
           THEN 'Windows 7' 
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '6.0'  
           THEN 'Windows Vista' 
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '5.2' 
           THEN 'Windows XP' 
        WHEN SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = '5.0' 
           THEN 'Windows 2000' 
        ELSE 'sys.Netbios_Name0' 
     END AS WindowsVersion
FROM
    fn_rbac_Add_Remove_Programs(1) sys1 
INNER JOIN  
    v_r_system sys ON sys.ResourceID = sys1.ResourceID  
LEFT OUTER JOIN 
    v_GS_COMPUTER_SYSTEM sys3 ON sys1.ResourceID = sys3.ResourceID
LEFT OUTER JOIN 
    v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP sys2 ON sys.Resourceid = sys2.ResourceID 
-- WHERE sys1.DisplayName0 LIKE '%' + @prodname + '%' AND sys1.version0 LIKE CONVERT(VARCHAR(10), @variable) + '%'

I get this error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '='.

Am I doing something wrong? Please let me know

Upvotes: 0

Views: 296

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

You are mixing the two possible syntax options for the case expression.

One being:

CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

and the other being:

CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

You can use either the first or the second, but not both in the same case expression.

Since all the expressions in your when parts are
sys.SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0) = <some value>, your case expression can be written using the first option:

 CASE sys.SUBSTRING(sys.Operating_System_Name_and0, PATINDEX('%[0-9]%', sys.Operating_System_Name_and0), sys.Operating_System_Name_and0)
    WHEN '6.3' 
       THEN 'Windows 8' 
    WHEN '6.2' 
       THEN 'Windows 8' 
    WHEN '6.1'  
       THEN 'Windows 7' 
    WHEN '6.0'  
       THEN 'Windows Vista' 
    WHEN '5.2' 
       THEN 'Windows XP' 
    WHEN '5.0' 
       THEN 'Windows 2000' 
    ELSE 'sys.Netbios_Name0' 
 END AS WindowsVersion

Upvotes: 1

abdul qayyum
abdul qayyum

Reputation: 555

sys.Operating_System_Name_and0 after case is redundent. so case statement should be like

SELECT ...
CASE /*comment col name here*/
WHEN substring(...) = ...
....

Upvotes: 0

Related Questions