?
Solved

Oracle LAG Function

Posted on 2011-05-04
2
Medium Priority
?
1,035 Views
Last Modified: 2013-12-07
Hi ,
I'm using Lag function to maintain balances, however, when the year changes , the closing balance from previous year not coming up as opening balance in the current year.
Here is the SQL;

nvl(lag(nvl(closing_balance,0),1) over(partition by
ACCOUNT_NO,YEAR,MONTH order by
ACCOUNT_NO,YEAR,MONTH),0)

Please see the last line year = 2011 , Opening Balanace is 0 instead of 50

Account Number Year      Month      Opening Bal      Credit      Debit      Closing Bal
999      2010      4      0      -10      20      10
999      2010      5      10      0      0      10
999      2010      6      10      -10      50      50
999      2010      7      50      0      0      50
999      2010      8      50      0      0      50
999      2010      9      50      0      0      50
999      2010      10      50      0      0      50
999      2010      11      50      0      0      50
999      2010      12      50      0      0      50
999      2011      1      0      -10      20      10
0
Comment
Question by:srini_pendy
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35693365
it's because your partition is for a single account/year/month.

Just partition by account, but keep the ordering


 NVL(
           LAG(NVL(closing_balance, 0), 1)
               OVER (PARTITION BY account_no ORDER BY account_no, year, month),
           0
       )
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35693380
you don't need the inner NVL

just the outer is sufficient


 NVL(LAG(closing_balance, 1) OVER (PARTITION BY account_no ORDER BY account_no, year, month),
           0
       )
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question