eric eric - 1 year ago 64
SQL Question

SQL case when with 2 different types of output [solved]


c1 c2 c3
'a1' 1 'hi'
'b1' 2 'hee'
'c1' 3 null

When I do

select case when c3 is null then c2 else c1 end from Table

I get a "Conversion failed when converting" error.

I know that case when can only return 1 type, but is there a way to convert varchar to int/tinyint? If not, is there another way?

Solution: I guess other than converting to int to varchar, there's no other way. But int>varchar works.

Answer Source
select case when c3 is null then CAST(c2 AS VARCHAR(10)) else c1 end from Table

you are mixing numeric and string data types. you will be required to use as string VARCHAR, CHAR, etc.

By casting your numeric data CAST(c2 AS VARCHAR(10)) you can solve the issue.

I guess for fun and to be more accurate in sql-server you could keep it as an integer but it would require to cast as sql_variant which would then make the entire field sql_variant and require casting for any use thereafter and you would gain 0 benefit because you could always test for IIF(ISNUMERIC(CASE)=1,CAST(value as INT),NULL).... Probably too much for this answer.


INSERT INTO @Table (C1, c2, c3)
VALUES ('a1', 1, 'hi'), ('b1',2,'hee'),('c1',3,null)

    ,CASE WHEN c3 is null THEN CAST(c2 AS sql_variant) else c1 end
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download