AW: How can i accumulate amount using SQL

Hi,

have tried  the SQL statemnt but can't work
message : Column *N or expression specified in SELECT list not valid.

Check your SQL statement, there may be a typo or you forgot the group by
clause at the end.

The following script works perfectly on my V5R2 machine:
CREATE TABLE QTEMP/TEST
      (NAME   CHAR (20)   NOT NULL WITH DEFAULT,
       AMOUNT DEC (9 , 0) NOT NULL WITH DEFAULT);

INSERT INTO QTEMP/TEST VALUES('Peter', 100);
INSERT INTO QTEMP/TEST VALUES('Mary', 100);
INSERT INTO QTEMP/TEST VALUES('John', 120);
INSERT INTO QTEMP/TEST VALUES('Mike', 200);

Select   a.Name, sum(a.Amount) as Summe,
         (Select    sum(b.Amount)
             from   test b
  where  a.Name >= b.Name) as Cumul
   from  test a
  group  by a.Name
  order  by a.Name;

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von simontcy
Gesendet: Freitag, 11. August 2006 12:13
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: How can i accumulate amount using SQL


have tried  the SQL statemnt but can't work
message : Column *N or expression specified in SELECT list not valid.

date: Wed, 09 Aug 2006 15:28:11 -0400
from: "Raul A. Jager W."
subject: Re: AW: How can i accumulate amount using SQL

I remember reading about UDF that can hold some info from call to call,
but I have never used it. Sounds worht to investigate, once you find
out how, a simple function will do what yuo need.

HauserSSS wrote:

Hi,

try the following SQL-Statement:

Select a.Name, sum(a.amount) as Total,
(Select sum(b.amount)
from test b
where a.Name >= b.Name) as Cumul
from test a
group by a.Name

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von 2046 BBQ
Gesendet: Mittwoch, 9. August 2006 17:53
An: RPG400-L@xxxxxxxxxxxx
Betreff: How can i accumulate amount using SQL


i want to do like that in sql

name amount accumulate(amount)
peter 100.00 100.00
John 100.00 200.00
Mary 120.00 320.00

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.