Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Assistances with a Case Statement Please

    [EXTENDED_SELL_UNIT_PRICE] = CASE WHEN ([EXTENDED_SELL] >= [UNIT_PRICE] or [UNIT_PRICE] = (NEGATIVE UNIT PRICE)) THEN 'Correct' ELSE 'Review' END

    My intent is to look for extended sell greater than or equal to unit price to flag an exception but I am trying to also negate any unit price that is negative then basically forget the condition.

    Thoughts on how to architect the case statement?

    I'm not sure how to handle negatives. I just started working with financial data about 4 months ago. I was on quality and geography data for the longest time.


    Kind regards
    V

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT CASE
             WHEN EXTENDED_SELL >= UNIT_PRICE OR UNIT_PRICE < 0 THEN
               'Correct'
             ELSE
               'Review'
           END As EXTENDED_SELL_UNIT_PRICE
    
    -- OR...
    
    SELECT CASE
             WHEN UNIT_PRICE < 0 THEN
               'Negative unit price'
             WHEN EXTENDED_SELL >= UNIT_PRICE THEN
               'Correct'
             ELSE
               'Review'
           END As EXTENDED_SELL_UNIT_PRICE
    ?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    First one works just fine. Thanks

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Is there a chance of Null values in the WHEN EXTENDED_SELL or UNIT_PRICE fields?
    If there is the possibility of Null, those values will be missed by this query (Null does not evaluate the same as 0).

    You can fix that like so:
    Code:
    SELECT CASE
       WHEN IsNull(EXTENDED_SELL,0) >= IsNull(UNIT_PRICE,0) OR IsNull(UNIT_PRICE,0) < 0 THEN
          'Correct'
       ELSE
          'Review'
       END As EXTENDED_SELL_UNIT_PRICE
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The Else portion will take care of the pesky NULLs already:
    Code:
    SELECT CASE
             WHEN EXTENDED_SELL >= UNIT_PRICE OR UNIT_PRICE < 0 THEN
               'Correct'
             ELSE
               'Review'
           END As EXTENDED_SELL_UNIT_PRICE
    FROM   (
            SELECT NULL As EXTENDED_SELL
                 , NULL As UNIT_PRICE
           ) As dummy_data
    Result
    Code:
    EXTENDED_SELL_UNIT_PRICE
    ------------------------
    Review
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ahhh, true. good call

  7. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thanks again I will review.

    You know experience is the best teacher no matter how many books I read I couldn't grasp 10% of the concepts now that I am immersed into Dataville the learning rate is 10 fold.

Posting Permissions

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