[odinsdream]

I've got this 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]

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

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

Originally posted by odinsdream
I've got this 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]

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 ;-))

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]

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 ;-))

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]

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

CAST(sd.Forced AS varchar(3))