Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    10

    Unanswered: Using a column as a result in CASE statement

    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?

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: Using a column as a result in CASE statement

    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
    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?

  3. #3
    Join Date
    Jun 2003
    Posts
    10
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2003
    Posts
    10
    Thanks andrewst! That did solve the problem. Here's the correct code to replace the 'F':

    CAST(sd.Forced AS varchar(3))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •