Post reply

Case expression problem

  • November 7, 2007 at 6:37 am

    #76813

    Hi.

    I'm trying to write a case statement that will query the maximum date from a table, and if the result is null- return getdate(). The following statement:

    select max(case somedate when NULL then getdate()

    else somedate

    end) somedate

    from trydates

    where somedate < (getdate()-4)

    will return max(somedate) even if the condition returns NULL (when the condition is:

    somedate < getdate()-40, there is no matching value and I get ' ' as a result). Is there a way I can catch that NULL returned when there is no date returned by the condition at the end? (Also- this needs to also work on Oracle... but don't mind the use in getdate(). It won't be in the final query)

    Thank you!

    Nili

  • November 7, 2007 at 6:45 am

    #749079

    I don't think you need to use CASE at all.

    SELECT

    ISNULL(MAX(somedate),GETDATE()) AS somedate

    FROM trydates

    WHERE somedate < (GETDATE()-4)

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 7, 2007 at 6:56 am

    #749082

    That's great!

    Only it doesn't work in Oracle.... That's the reason I didn't use the "decode" function in Oracle- it does exactly what this function does but doesn't work for SQL Server....

    Can you help me translate the exact statement you wrote to a case statement that WILL catch the NULL?...

  • November 7, 2007 at 7:01 am

    #749085

    :w00t: I missed that little Oracle reference.

    Try this: (moving your MAX statement inside the CASE)

    SELECT

    CASE WHEN MAX(somedate) IS NULL THEN GETDATE() ELSE somedate END AS somedate

    FROM trydates

    WHERE somedate < (GETDATE()-4)

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 7, 2007 at 7:25 am

    #749094

    .... And it worked!! For both Oracle and SQL Server.

    Thanks!

  • November 7, 2007 at 7:10 pm

    #749318

    Nili S (11/7/2007)

    .... And it worked!! For both Oracle and SQL Server.

    Thanks!

    GETDATE() worked in Oracle? I don't think so...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 7, 2007 at 7:14 pm

    #749319

    ... and, by the way, COALESCE works in both Oracle and SQL Server ...

    ... and NVL is the Oracle equivelent for ISNULL in SQL Server...

    ... and CURRENT_TIMESTAMP works in both Oracle and SQL Server as a substitute for Oracle's SYSDATE and SQL Server's GETDATE()...

    ... and 100% code portability is a myth except for the simplest of SQL ... 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 8, 2007 at 5:55 am

    #749414

    Thanks Jeff! I've never worked with Oracle so that info was very informative ... :hehe:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 8, 2007 at 10:33 pm

    #749711

    Jason Selburg (11/8/2007)

    Thanks Jeff! I've never worked with Oracle so that info was very informative ... :hehe:

    You bet, Jason... thanks for the feedback...

    ps. I hate Oracle 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply