Using ROW_NUMBER() as the offset in the LAG function

[former_member1220589]

Hello,

I'm trying to create a running row-by-row total using this query, but when I use the ROW_NUMBER() as the offset within the LAG function it returns null values in that column. If I change the offset to a raw number it works correctly. Is there a way to reference ROW_NUMBER() in the lag function?

SELECT X."Quantity", X."Net Qty", X."Row", Lag(X."Quantity",X."Row") over (Order by X."LI")

From

(

SELECT T1."LineNum"+1 "LI",

T1."Quantity",

Lag(T1."Quantity",1) over (Order by T1."LineNum")-T1."Quantity" "Net Qty",

Cast((ROW_NUMBER() OVER (ORDER BY T1."LineNum")) as int) "Row"

FROM OQUT T0 INNER JOIN QUT1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T0."DocNum" = 1073697

)

X

[fedaros]

Hi Peter,

You don't need row_number for this, you can partition by document. Check if this example works for your need:

select
  doc,item,quant,
  quant + ifnull(lag(quant) over (partition by (doc) order by item),0) as balance
from --dummy source
(select 100 as doc,1 as item, 10 as quant from dummy union
 select 100 as doc,2 as item, 20 as quant from dummy union
 select 100 as doc,3 as item,300 as quant from dummy union
 select 200 as doc,1 as item, 40 as quant from dummy)

Regards, Fernando Da Rós