Re: Fetch the correct record - pls help

[Nisha Ramesh <n4nisha@xxxxxxxxx>]

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.