CODEWITHSUNDEEP
sqloracle-databaselagwindow-functions
Gavin
Gavin

Reputation: 167

Oracle SQL LAG Function

I'd appreciate some help with this code, I'm getting a 'missing keyword' error. I've never used the Lag function before, so hopefully I using it correctly. Thanks for your help. Gav

CREATE VIEW GS_Date AS
SELECT                        
        DATE_DATE, 
        DATE_FLAG,
        CASE WHEN  LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE ) = '1' THEN DATE_STEP = ( LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE ) ) + '1'
                 WHEN  LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE ) = '0' AND LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE ) =  '-1'  THEN DATE_STEP = ( LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE ) ) + '1'
                 ELSE DATE_STEP = LAG ( DATE_FLAG)  OVER ( ORDER BY DATE_DATE )  END AS DATE_STEP
FROM DATE_GROUP

Upvotes: 1

Views: 1485

Answers (3)

MT0
MT0

Reputation: 167981

So you don't have to keep writing LAG( ... ) OVER ( ... ) statements, get the LAG value in a sub-query and then use CASE or DECODE in the outer query:

CREATE VIEW GS_Date AS
SELECT  DATE_DATE,
        DATE_FLAG,
        DECODE(
          DATE_STEP,
          1, 2,
          0, 1,
          -1, 0,
          DATE_STEP
        ) AS DATE_STEP
FROM    (
  SELECT  DATE_DATE, 
          DATE_FLAG,
          LAG ( DATE_FLAG )  OVER ( ORDER BY DATE_DATE ) AS DATE_STEP
  FROM    DATE_GROUP
)'

Also, your second WHEN clause will never be true:

WHEN LAG ( DATE_FLAG ) OVER ( ORDER BY DATE_DATE ) = '0'
AND  LAG ( DATE_FLAG ) OVER ( ORDER BY DATE_DATE ) = '-1'
THEN ...

Since the value can never be both -1 and 0. I've assumed you meant to use OR rather than AND.

Upvotes: 0

user5683823
user5683823

Reputation:

The problem is with the CASE expression; you were using LAG correctly.

Other points: Don't add strings like '1' and '-1' to numbers. Add numbers - you don't need the single quotes.

Also, if in a computation something is common and only the "last part" is different, you can use the CASE expression "at the end". Like below:

Note: On re-reading the original post, the formula needs to be more complicated (I didn't get it exactly right). Not changing the answer, since it still illustrates the same ideas I meant to share. BUT: Looking at the original post, there is a condition "when LAG = 0 and LAG = -1" - that can never be true. What was meant is probably "OR" instead of "AND". In the formula I wrote below, this means one more WHEN...THEN... branch.

LAG(DATE_FLAG) OVER (ORDER BY DATE) 
       + CASE LAG(DATE_FLAG) OVER (ORDER BY DATE ) WHEN 1 THEN  1
                                                   WHEN 0 THEN -1
                                                   ELSE         0  END AS DATE_STEP

Further edit: Looking at it again, it seems when the flag is 1, 0 or -1 then we must add 1, otherwise add 0... then it's easier to use a "simple CASE expression" instead of a "searched CASE expression" as I did. Something like:

LAG(...) ...
   + CASE WHEN LAG(...) ... IN (-1, 0, 1) THEN 1
          ELSE                                 0 END AS DATE_STEP

Upvotes: 2

Kamil Ibadov
Kamil Ibadov

Reputation: 710

Try like this

CREATE VIEW GS_Date AS
SELECT DATE_DATE,
       DATE_FLAG,
       CASE
         WHEN LAG(DATE_FLAG) OVER(ORDER BY DATE_DATE) = '1' THEN
          (LAG(DATE_FLAG) OVER(ORDER BY DATE_DATE)) + '1'
         WHEN LAG(DATE_FLAG) OVER(ORDER BY DATE_DATE) = '0'   AND LAG(DATE_FLAG)  OVER(ORDER BY DATE_DATE) = '-1' THEN
          (LAG(DATE_FLAG) OVER(ORDER BY DATE_DATE)) + '1'
         ELSE
          LAG(DATE_FLAG) OVER(ORDER BY DATE_DATE)
       END AS DATE_STEP
  FROM DATE_GROUP

Upvotes: 0

Related Questions