SUGGESTION: INTEGER and BIGINT are not handled in CASE statements and throwing error.

Question

We are forced change the datatype from BIGINT to VARCHAR to handle case statement.

select distinct QUESTION_CODE, CASE
QUESTION_CODE WHEN '20061'
THEN 'UNKNOWN'
ELSE QUESTION_CODE
END AS HRA_STANDARD_ANSWER
from XXXXXX.HRA_QUESTION_ANSWER

Steps to reproduce:

Table Definition :

CREATE TABLE [XXXXX].[HRA_QUESTION_ANSWER]
(

[QUESTION_CODE] [bigint]  NULL

)
WITH
(
DISTRIBUTION = HASH ( [QUESTION_CODE] ),
CLUSTERED COLUMNSTORE INDEX
)

Load sample data into table and run above failed query.

Answer 1

Hi @Ayalasomayajula, Suryakumar ,

Thanks for using Microsoft Q&A !!
I do not think it is an issue with the bigint or int until I am missing anything over here. Please find below the query I used to validate the same -

You will get a conversion error when you run your query as you are trying to display QUESTION_CODE without converting it to a VARCHAR because HRA_STANDARD_ANSWER is implicitly being considered as a VARCHAR due to your THEN part (as 'UNKNOWN' is a text). You will need to explicitly convert the value to VARCHAR to be able to displayed/stored as HRA_QUESTION_ANSWER like below -

Thanks
Saurabh