SQL LAG IN CASE STATEMENT

Question

I would appreciate any pointers on what is wrong with my case statement, if the Current CLUSTERn = Previous CLUSTERn Then add the Previous PRODCAT to the current line as PREVCAT...

ORA-30484: missing window specification for this function
30484. 00000 -  "missing window specification for this function"
*Cause:    All window functions should be followed by window specification,
           like <function>(<argument list>) OVER (<window specification>)
*Action:
Error at Line: 11 Column: 30

SELECT  CLUSTERn,
        MEMBERn,
        COUNT(*) OVER ( PARTITION BY CLUSTERn ORDER BY MEMBERn, PRODCAT, STARTd, ENDd ) AS NEWRANK,
        CASE WHEN CLUSTERn = LAG(CLUSTERn) THEN LAG(PRODCAT) ELSE 'New' END AS PREVCAT,
        STATUS,
        PRODCAT,
        JOINTYPE,
        JOINRANK,
        CSP,
        PROGID,
        PROMNAME,
        PROMOID,
        COHORT,
        FWEEK,  
        STARTd,
        ENDd,
        SOURCE
FROM(

Your query is very incomplete. You are in a much better position to judge what is wrong with your query. With more information, we would be better able to help you (error messages if any, input/output (expected and actual) etc.) — HoneyBadger, Jun 22 '18 at 09:42
For starters: `CLUSTERn = LAG(CLUSTERn) = LAG(PRODCAT)` is wrong you can't "append" equality conditions like that (regardless where you use them) — , Jun 22 '18 at 09:46
@Gavin LAG is an analytic function, so you need to provide the window specification for it, just like you did for the analytic version of COUNT that you used in the previous line. If you are unclear on analytic functions, I highly recommend you read up on them. In your case, only you know what defines "previous row" (since you didn't tell us that bit of logic); that's what needs to go inside the (currently missing) `OVER ()` clause to tell Oracle how to pick the previous row. — Boneist, Jun 22 '18 at 10:10

score 6 · Answer 1 · answered Jun 22 '18 at 10:30

I'm not sure what the confusing is. You have:

(CASE WHEN CLUSTERn = LAG(CLUSTERn)
      THEN LAG(PRODCAT)
      ELSE 'New'
 END) AS PREVCAT,

You are missing the OVER clause -- pretty fundamental for all window functions.

Without sample data it is pretty hard to figure out what you really want. Perhaps:

(CASE WHEN CLUSTERn = LAG(CLUSTERn) OVER (ORDER BY MEMBERn, PRODCAT, STARTd, ENDd)
      THEN LAG(PRODCAT) OVER (ORDER BY MEMBERn, PRODCAT, STARTd, ENDd)
      ELSE 'New'
 END) AS PREVCAT,

It is also possible that no CASE is required. LAG() has a three-argument form that allows you to specify a default value:

LAG(PRODCAT, 1, 'NEW') OVER (PARTITION BY ClusterN ORDER BY STARTd, ENDd)

SQL LAG IN CASE STATEMENT

1 Answers1