Re: Fetch the correct record - pls help

On 19 Oct 2012 09:51, Nisha Ramesh wrote:

I have a scenario like this <<SNIP; replace w/ a script>>

create table File1 (AcN CHAR)
;
insert into File1 values
('A'),('B'),('C')
;
create table File2 (AcN CHAR, Sec CHAR, Qty DEC)
insert into File2 values
('A', 'x', 2), ('A', 'x', 3), ('A', 'x', 1)
, ('A', 'y', 2), ('A', 'y', 3)
, ('B', 'y', 5), ('B', 'y', 1)
, ('C', 'x', 2), ('C', 'x', 2)
;

I want to create File3 as a summary. The value should be like this

AcN Sec Qty
A x 6
A y 5
B y 6
C x 4

Without using SQL (I have to change the whole program logic if I go
for SQL)

Maybe it is just me, but I would not consider using one SQL INSERT statement to replace entirely both the I\O and summarize logic portion of the program as having changed "the whole program logic".

<<SNIP>>
Anyone has any better idea? My logic seems to be complex.. :(

Ignoring File1 per no obvious need to reference that file having been stated, the SQL:

insert into file3
select Accnum, Security, sum(Qty) as Qty
from file2
group by Accnum, Security

Although "create File3" was stated, the implication that the RPG would do WRITE to an existing file seems contradictory to that claim. The same SQL SELECT statement but within a CREATE TABLE File3 [what was intended to be] offered by Birgitta's post would actually create the file WITH DATA.