RE: Fetch the correct record - pls help

If you have to do this in RPG, then you will need to accumulate the Qty field into another field, and when the keys change, after you have written the record to File3, clear the field that you were accumulation qty into, ready for the next set of records

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nisha Ramesh
Sent: Friday, October 19, 2012 2:34 PM
To: RPG programming on the IBM i / System i
Subject: Re: Fetch the correct record - pls help

The complete program logic is this

I have a file which has 6 different account numbers. For each of this account number, I need to check in File1 which has account number, security and Quantity. I need to sum up the qty field for a combination of Acc num and security. Once I get the security, I need to search in another file say
File2 for SecRate corresponding to the security which I got from File1.
Apart from the Qty sum up, I have done the program with normal file operation. But the Qty part seems to be complex without SQL. But I havent written any SQL so far. So Please show me how to write query for this.
Thanks for all of your time
I need to write the output in a summary file.

On 19 October 2012 23:49, Nisha Ramesh <n4nisha@xxxxxxxxx> wrote:

Can anyone show me the complete query for this? I am new to SQL/400..


On 19 October 2012 21:24, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Am I wrong or do you need something like this:

Create Table as
(Select Accnum, Security, sum( Qty) Qty
From Table2
Group By AccNum, Security)
With Data;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
Im
Auftrag von Nisha Ramesh
Gesendet: Friday, 19.10 2012 16:52
An: RPG programming on the IBM i / System i
Betreff: Fetch the correct record - pls help

Hi All,

I have a scenario like this
I have 3 files , File1, File2, File3.

File1 has account number as a filed
File2 has account number, security and Quantity as fields
File3 has Account number Quantity and Security.

suppose File1 has these values
A
B
C

File2 - Accnum and Sec are keyfileds
Accnum Security Qty
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

AccNum Security 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)
By using chain or Reade how can I achieve this in mininum steps

I have used a method like this
Point to the first record in file1 and read from there onwards. With
Accnum as the key readE file2 in a loop( the file has accnum and
security as keufields but am using Accnum alone not sure whether that
will cause any
probs) , save the previous security value and in the DoW not
%Eof(File2)loop check whether the prev saved security = Current
security, If so sum up the Qty fields, else, write to file3. After
endDo, I put the write part again otherwise I was missing the last
record which will not enter into the DoW not %Eof loop.

Anyone has any better idea? My logic seems to be complex.. :(
--
This is the RPG programming on the IBM i / System i (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.


--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.


Disclaimer: This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.