question

AyalasomayajulaSuryakumar-3071 avatar image
0 Votes"
AyalasomayajulaSuryakumar-3071 asked SaurabhSharma-msft answered

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

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.

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered

Hi @ayalasomayajulasuryakumar-3071,

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 -
131226-image.png

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 -
131251-image.png

Thanks
Saurabh


image.png (19.9 KiB)
image.png (22.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ayalasomayajulasuryakumar-3071,

Please let me know if you have any other questions.

Thanks
Saurabh

0 Votes 0 ·
SaurabhSharma-msft avatar image SaurabhSharma-msft SaurabhSharma-msft ·

Hi @ayalasomayajulasuryakumar-3071,

I have not heard back from you. Did my answer solve your issue? If so, please mark as accepted answer. If not, please let me know how I may better assist.

Thanks
Saurabh

0 Votes 0 ·