Using a column as a result in CASE statement

odinsdream · #1 (**permalink**) 06-18-03, 13:53

I've got this code:

Code:

CASE WHEN sd.ActiveSite = 1 THEN
     CASE WHEN sd.Forced IS NULL THEN
           'x'
     ELSE
           'F'
     END
ELSE
     ''
END

However, how can I change it so that instead of "F" being returned, I return the value of sd.Forced?

dbmadcap · #2 (**permalink**) 06-18-03, 14:18

You have the answer in front of you dude :-) Just do what you want and it works like a charm !!

Code:

CASE WHEN sd.ActiveSite = 1 THEN
     CASE WHEN sd.Forced IS NULL THEN
           'x'
     ELSE
           sd.Forced
     END
ELSE
     ''
END

Quote:

Originally posted by odinsdream
I've got this code:

Code:

CASE WHEN sd.ActiveSite = 1 THEN
     CASE WHEN sd.Forced IS NULL THEN
           'x'
     ELSE
           'F'
     END
ELSE
     ''
END

However, how can I change it so that instead of "F" being returned, I return the value of sd.Forced?

odinsdream · #3 (**permalink**) 06-18-03, 14:30

I get an error with that method:

Syntax error converting varchar value 'x' to a column of data type tinyint.

Perhaps the problem is elsewhere? This is the full code: (you've seen it before ;-))

Code:

SELECT 
MAX(CASE WHEN sd.ActiveSite = 1 THEN case WHEN sd.Forced IS NULL THEN 'x' ELSE sd.Forced END ELSE '' END),
...
...

Did I miss something? It seemed to me that this should have worked, too.

andrewst · #4 (**permalink**) 06-19-03, 07:56

Quote:

Originally posted by odinsdream
I get an error with that method:

Syntax error converting varchar value 'x' to a column of data type tinyint.

Perhaps the problem is elsewhere? This is the full code: (you've seen it before ;-))

Code:

SELECT 
MAX(CASE WHEN sd.ActiveSite = 1 THEN case WHEN sd.Forced IS NULL THEN 'x' ELSE sd.Forced END ELSE '' END),
...
...

Did I miss something? It seemed to me that this should have worked, too.

It appears that the parser has decided that the inner case statement should return a tinyint value - presumably because sd.Forced is a tinyint column? So the solution would be to convert sd.Forced to a character string - something like this:

case WHEN sd.Forced IS NULL THEN 'x' ELSE TO_CHAR(sd.Forced) END

I'm not sure if TO_CHAR is the right function name for your DBMS, but if not there must be an equivalent.

odinsdream · #5 (**permalink**) 06-24-03, 10:25

Thanks andrewst! That did solve the problem. Here's the correct code to replace the 'F':

CAST(sd.Forced AS varchar(3))

Internet Services

Web Development

Digital Marketing

Consumer Tech