What happens when I pass an CASE expression to the LAST

My LAST_VALUE function looks somethin like this

     LAST_VALUE(
         CASE 
             WHEN statement_1 then 0
             WHEN statement_2 then 1
             WHEN statement_3 then 0
             ELSE NULL
         END IGNORE NULLS)  OVER (PARTITION BY column1 ORDER BY column2)

Can someone explains what value is the LAST_VALUE supposed to return if there is expression. I understand what happens when a column is passed, but incase of such expressions no clue whatsoever.

CodePudding user response：

You can find the description and examples at https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions085.htm#SQLRF00655 Regards... Btw. the CASE ... END structure is just as you are selecting values from the table field but with If condition. It doesn't affect the LAST_VALUE function. It is something like this: CASE WHEN ID = 1 THEN 10 WHEN ID = 2 THEN 20 ELSE 99 END where ID = 1 is Statement1, ID = 2 is Statement2 ... and so on..

CodePudding user response：

The same as if your case expression were itself a column...

SELECT
  *,
  LAST_VALUE(new_column IGNORE NULLS)
    OVER (PARTITION BY column1
              ORDER BY column2
    )
FROM
(
   SELECT
     *,
     CASE 
         WHEN statement_1 then 0
         WHEN statement_2 then 1
         WHEN statement_3 then 0
         ELSE NULL
     END
       AS new_column
   FROM
     your_table
 )
   sub_query