Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Question Unanswered: Select When/Then?

    I'm trying to complete a query.

    I have two numeric columns.
    If a is larger than b I want it to return 'Over'
    when a is less than b I want it to return 'Under'
    when they're even I want it to return 'even'

    Something Like this...

    Select When ColA > ColB Then 'OVER' ElseIf colA<ColB Then 'Under' Else 'even', FirstName, Position, from Table
    How can I use WHEN in a Select clause to do this? (Or can this type of query not be done?)

  2. #2
    Join Date
    Mar 2007
    Posts
    24
    Try this

    SELECT CASE WHEN COl1>COl2 THEN 'Over' ELSE 'Under' END
    FROM Table

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    for above u can write the query as below
    select "Status" =
    case
    when ColA>ColB then "over"
    when colA<ColB then "under"
    else "even"
    end, FirstName, Position from Table

  4. #4
    Join Date
    Feb 2007
    Posts
    20
    Thanks....

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT
       CASE
          WHEN colA = colB THEN 'Even'
          WHEN colA < colB THEN 'Under'
          WHEN colA > colB THEN 'Over'
          ELSE 'Not comparable!'
       END AS Status
       FROM table
    Now, you job is to tell me why I'd do such a silly thing (yes, there is a reason).

    -PatP

  6. #6
    Join Date
    Mar 2007
    Posts
    24
    I guess you are considering NULL Values
    Am I right?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Correct, NULL values are the most likly form of non-comparable items.

    -PatP

Posting Permissions

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