CODEWITHSUNDEEP
sqlsql-server
Munna Donepudi
Munna Donepudi

Reputation: 61

CASE STATEMENT IN SQL

Select CASE WHEN loc ='J' THEN 'Y'
            WHEN loc NOT IN ('J') THEN 'N'
            ELSE 0 END AS loc FROM [prd].[dbo].[GRP]  
            EXCEPT
Select location FROM [prd1].[dbo].[GRPO] WHERE VALUE IN('Y','N');

Error:

Conversion failed when converting the varchar value 'Y' to data type int.

both the columns in both the tables are of same data type (varchar) only.

Upvotes: 2

Views: 142

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Case statement returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Since 0 (INT) has higher precedence than Y (Varchar), Y is implicitly converted to INT so you are getting conversion error.

Change 0(INT) to '0'(String) in ELSE statement 

Select CASE WHEN loc = 'J' THEN 'Y'
            WHEN loc <> 'J' THEN 'N' -- loc NOT IN ('J')
ELSE '0' END

Upvotes: 5

Related Questions