Help with Case When statement

Doolius

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

Frank Kulash

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.

BluShadow

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

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

Thanks for the quick reply Frank.

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

Frank Kulash

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.