topic Re: An SQL chalenge in Application Development and Automation Discussions

An SQL chalenge

former_member202733 — Mon, 18 Oct 2021 20:13:25 GMT

Hi guys, how are you today?

After burn a few neurons with part of my requirement, I'm forced to ask for your help 😄

I would like to solve this with pure SQL sentences in an AMDP method, I could solve this easily in ABAP, even in an AMDP method using loops, but as I said, I would like to solve this with SQL sentences, without use any kind of loops.

Please take a look on this image:

I have 2 columns, the first I'll name as D and the second as E

The D column, is a result of a SELECT SUM, but the E colunm, is a calculated column, and it shoud work as follow:

Firsrt line are equals in both columns. E1 = D1
In the second line, the calculation for the second column is E1 + D2
In the third line, the calculation for the second column is E2 + D3
In the forth line, the calculation for the second column is E3 + D4

And so on.

So tha's it. Is it posible solve this with pure SQL sentences?

Could someone please give me a help?

Best regards.

Ronaldo S. Vieira

Re: An SQL chalenge

Sandra_Rossi — Tue, 19 Oct 2021 03:29:08 GMT

Also asked here with currently one answer proposed.

Re: An SQL chalenge

former_member202733 — Tue, 19 Oct 2021 13:38:19 GMT

sandra.rossi begging for your pardon. I ask in both forum (and ask here first) because stackoverflow is not only a SAP forum and other people that non SAP people could help me. The answer provided on the messge in stackoverflow, I've try it but it does not work.

By the way I'm a big fan of your posts.

Best regards

Ronaldo S. Vieira

Re: An SQL chalenge

ThorstenHoefer — Tue, 19 Oct 2021 14:52:06 GMT

Hi ronaldo.silveiravieria,

Open SQL offers windows functions now.

https://help.sap.com/doc/abapdocu_755_index_htm/7.55/en-US/abensql_win_func.htm

The window functions LEAD or LAG are suitable for calculations, such as determining the difference between values in the current row and values of the preceding or following rows.

It could also works like this:

https://stackoverflow.com/questions/30861919/what-is-rows-unbounded-preceding-used-for-in-teradata/30862368

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, sum(a) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
ORDER BY t

Re: An SQL chalenge

Sandra_Rossi — Tue, 19 Oct 2021 16:50:10 GMT

No problem. I'm just mentioning it for people who answer here so that they can know if the issue was solved and how. Thanks for the feedback.