This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,795 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

Help with Case When statement

Doolius
Doolius Member Posts: 115
edited Mar 24, 2014 11:33AM in SQL & PL/SQL

I am trying to compare the "LATEST_EVENT_DT" (date) to the "Greatest Date" (date).

Then for the "Latest Event", display 'CANCELLED' if the "Greatest Date" is greater than the 'LATEST_EVENT_DT" and if not, then display the value in the "LATEST_EVENT_DESC"  column.

If I take out the "Latest Event" part everything works fine. But with it in I get the error "FROM keyword not found where expected". Can someone help with this?

select

   LATEST_EVENT_DT,


   "OIT_Closed_Date", "OIR_Closed_Date", "OIN_Closed_Date",

   greatest(LATEST_EVENT_DT, nvl( "OIT_Closed_Date",TO_DATE('01011950', 'MMDDYYYY')), nvl("OIR_Closed_Date",TO_DATE('01011950', 'MMDDYYYY')) ,nvl( "OIN_Closed_Date",TO_DATE('01011950', 'MMDDYYYY')))   "Greatest Date",

"Latest Event" =
CASE
WHEN LATEST_EVENT_DT < "Greatest Date" THEN 'CANCELLED'
ELSE LATEST_EVENT_DESC
END

from apex01.greatest_date_vw_SGD

Thanks,

Steven

Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Mar 24, 2014 11:25AM Answer ✓

    Hi, Steven,

    What is

    "Latest Event" =
     

    supposed to do?  Do you want the column produced by the CASE expression to appear as "Latest Event" in the output?  If so, use a column alias after the expression, like you did with "Greatest Date".

    You can't define a column alias (like "Greatest Date") and then reference that alias in the same SELECT clause where it was defined.  Derive the column in a sub-query, then you can reference it in a super-query, like this:

    WITH    got_greatest_date    AS
    (
        select  LATEST_EVENT_DT
        ,       "OIT_Closed_Date"
        ,       "OIR_Closed_Date"
        ,       "OIN_Closed_Date"
        ,       greatest ( LATEST_EVENT_DT
                          , nvl ( "OIT_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          , nvl ( "OIR_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          , nvl ( "OIN_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          )        AS "Greatest Date"
        from    apex01.greatest_date_vw_SGD
    )
    SELECT  g.*
    ,       CASE
                WHEN  LATEST_EVENT_DT < "Greatest Date"
                THEN  'CANCELLED'
                ELSE  LATEST_EVENT_DESC
            END     AS "Latest Event"
    FROM    got_greatest_date  g
    ;
     

    Do you really like the double-quoted names?  Most people find them very confusing, and much more trouble than they're worth.

Answers

  • BluShadow
    BluShadow Mr UKMember, Moderator Posts: 42,708 Red Diamond

    Like this?

    select LATEST_EVENT_DT, 
           "OIT_Closed_Date",
           "OIR_Closed_Date",
           "OIN_Closed_Date",
           "Greatest Date",
           CASE
             WHEN LATEST_EVENT_DT < "Greatest Date" THEN 'CANCELLED'
             ELSE LATEST_EVENT_DESC
           END AS "Latest Event"
    from (
          select LATEST_EVENT_DT, 
                 "OIT_Closed_Date",
                 "OIR_Closed_Date",
                 "OIN_Closed_Date",
                 greatest(LATEST_EVENT_DT, nvl( "OIT_Closed_Date",TO_DATE('01011950', 'MMDDYYYY')), nvl("OIR_Closed_Date",TO_DATE('01011950', 'MMDDYYYY')) ,nvl( "OIN_Closed_Date",TO_DATE('01011950', 'MMDDYYYY'))) "Greatest Date"
          from apex01.greatest_date_vw_SGD
         )
    
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Mar 24, 2014 11:25AM Answer ✓

    Hi, Steven,

    What is

    "Latest Event" =
     

    supposed to do?  Do you want the column produced by the CASE expression to appear as "Latest Event" in the output?  If so, use a column alias after the expression, like you did with "Greatest Date".

    You can't define a column alias (like "Greatest Date") and then reference that alias in the same SELECT clause where it was defined.  Derive the column in a sub-query, then you can reference it in a super-query, like this:

    WITH    got_greatest_date    AS
    (
        select  LATEST_EVENT_DT
        ,       "OIT_Closed_Date"
        ,       "OIR_Closed_Date"
        ,       "OIN_Closed_Date"
        ,       greatest ( LATEST_EVENT_DT
                          , nvl ( "OIT_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          , nvl ( "OIR_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          , nvl ( "OIN_Closed_Date"
                                , TO_DATE ('01011950', 'MMDDYYYY')
                                )
                          )        AS "Greatest Date"
        from    apex01.greatest_date_vw_SGD
    )
    SELECT  g.*
    ,       CASE
                WHEN  LATEST_EVENT_DT < "Greatest Date"
                THEN  'CANCELLED'
                ELSE  LATEST_EVENT_DESC
            END     AS "Latest Event"
    FROM    got_greatest_date  g
    ;
     

    Do you really like the double-quoted names?  Most people find them very confusing, and much more trouble than they're worth.

  • Doolius
    Doolius Member Posts: 115

    Thanks for the quick reply Frank.

    Only thing I had to add was "LATEST_EVENT_DESC" into the first select statement.

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi, Steven,

    StevenD609 wrote:
    
    Thanks for the quick reply Frank.
    
    Only thing I had to add was "LATEST_EVENT_DESC" into the first select statement.
    

    Good catch!  Since I didn't have a copy of your table, I couldn't test it.

This discussion has been closed.