Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I sum the previous row with LAG() function?

Best regards,
I am using the following function (LAG) to sum the previous row: 

(LAG(INV_FINAL, 1, 0) OVER (PARTITION BY DISTRIBUIDORA ORDER BY PERIODO ASC) + LAG(INV_FINAL, 2, 0) OVER (PARTITION BY DISTRIBUIDORA ORDER BY PERIODO ASC)) + INV_FINAL AS RESULT

But it is not giving me the expected result as can you see:
image.pngThe correct result should be :
image.pngI am attentive to your comments, I would be very grateful if you can help me, thank you.
This post has been answered by Paulzip on Oct 7 2021
Jump to Answer

Comments

Suri
Hi,

Maximum size for VARCHAR2 is 4000. So you cant add 32767 characters to a variable which you have defined with VARCHAR2 datatype.

Below will work
DECLARE
  LEN1  NUMBER;
  STR   VARCHAR2(32767);
BEGIN
  
  STR := RPAD('*', 4000, '*');
  
  SELECT LENGTH(STR) INTO LEN1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LEN1: '||LEN1);
 
END;
Thanks,
Suri

Edited by: Suri on Aug 8, 2012 1:50 PM

Edited by: Suri on Aug 8, 2012 1:52 PM
APC
In your first example the database engine overrode your length for the RPAD() call with the SQL limit (4000 characters). In your second example you presented the engine with a 32K string and it hurled. This is because the engine hasn't been programmed to truncate a string which is too long.

If anything I think the first example is inconsistent. Certainly that behaviour is [url http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions147.htm#i78723]not documented.

Cheers, APC
1 - 2

Post Details

Added on Oct 7 2021
#oracle, #sql, #sqldeveloper
5 comments
6,719 views
2 mentions