Make various Total during read

[raffaeu]

I guys i have another issue for you. I have a file with two columns.

CODE    PAX
AA         10
AB         12

... ...

AA          3
AB          4

I read the file with a Chain and i need to make Total for each code but i don't know how many diffrent code i have.
During i read i need to write in a new file the code with the total Pax.
Using SQL inside i make this with a GROUP BY and a SUM(PAX).

Unfortunately my Boss doesn't know SQL and she wants a solution only using RPG ILE. I write code in free format.

Someone can help me?

[DeadManWalks]

Re: Make various Total during read

Hello,
you can use SQL in your free form code.

 // Prepare the statement                 
 ExSr @Prepare;                           
                                          
 // Declare the cursor                    
 ExSr @Declare;                           
                                          
 // Open the Cursor                       
 ExSr @OpenCursor;                        
                                          
 // Read through until EOF                
 DoU SqlCod <> 0;                         
     ExSr @Fetch;                         
     If SqlCod = 0;  // Successful IO     
     // Do something with it              
         
     EndIf;                
 EndDo;                    
 ExSr  @Close; 

Don't get me started about bosses who live in the past.

[jamief]

Re: Make various Total during read

~
      *
      * Program Info
      *
     d                SDS
     d  @PGM                 001    010
     d  @PARMS               037    039  0
     d  @MSGDTA               91    170
     d  @MSGID               171    174
     d  @JOB                 244    253
     d  @USER                254    263
     d  @JOB#                264    269  0
      *
      * Variables
      *
     d Chr4            s              4
     d Count           s              3  0
     d Codes           s              2    dim(5000)
     d CodeTotal       s              9  2 dim(5000)
     d workcode        s              2
     d work$           s              9  2
     d X               s              4  0
     d Y               s              4  0

      /Free
       //---------------------------------------------------
       //       C a l c u l a t i o n  S p e c s
       //---------------------------------------------------
             for count = 1 to 10;

               select;
                 when count = 1;
                   workCode = 'AA';
                   work$ = 1.99;
                 when count = 2;
                   workCode = 'AB';
                   work$ = 9.16;
                 when count = 3;
                   workCode = 'AB';
                   work$ = 4.11;
                 when count = 4;
                   workCode = 'AC';
                   work$ = 8.25;
                 when count = 5;
                   workCode = 'AX';
                   work$ = 6.66;
                 when count = 6;
                   workCode = 'AR';
                   work$ = .59;
                 when count = 7;
                   workCode = 'AA';
                   work$ = 16.00;
                 when count = 8;
                   workCode = 'AF';
                   work$ = 11.58;
                 when count = 9;
                   workCode = 'AF';
                   work$ = 2.33;
                 when count = 10;
                   workCode = 'AZ';
                   work$ = 5.77;
               endsl;

               Y = %lookup(workcode:codes);
               if Y = *zeros;
                 x+=1;
                 codes(X) = workcode;
                 codetotal(X) = work$;
               else;
                 codetotal(Y) += work$;
               endif;

             endfor;
                         *inlr = *on;
       //---------------------------------------------------
      /End-Free 

[raffaeu]

Re: Make various Total during read

Thanks but if is not a problem could you also include the C+ code for a simple SELECT? Thanks.

[raffaeu]

Re: Make various Total during read

thanks tomorrow i will try in my office now is 18:00 PM ... and i will respond you...
Thank so much!

[jamief]

Re: Make various Total during read

I could supply a C+ example I will just need a few weeks to learn the language

jamie

[raffaeu]

Re: Make various Total during read

Now i mistake my explanation ...
I mean you wrote me 3 subProc wich maybe include some SQL statment ...
Now i don't know wich SQL st i need to put in the C+ zone of my RPG LE program ...


For example i need to run and read this query

SELECT CODE, COUNT(PAX) FROM TABLE GROUP BY CODE

and read all the records that this query return me. How i do this in RPGLE?

[jamief]

Re: Make various Total during read

Im not exactly following you.

you said

Unfortunately my Boss doesn't know SQL and she wants a solution only using RPG ILE. I write code in free format.

so I posted the code in RPG-FREE
Im not sure what else you need.

~
      *
      * Program Info
      *
     d                SDS
     d  @PGM                 001    010
     d  @PARMS               037    039  0
     d  @MSGDTA               91    170
     d  @MSGID               171    174
     d  @JOB                 244    253
     d  @USER                254    263
     d  @JOB#                264    269  0
      *
      * Variables
      *
     d Chr4            s              4
     d Count           s              3  0
     d Codes           s              2    dim(5000)
     d CodeTotal       s              9  2 dim(5000)
     d workcode        s              2
     d work$           s              9  2
     d X               s              4  0
     d Y               s              4  0

      /Free
       //---------------------------------------------------
       //       C a l c u l a t i o n  S p e c s
       //---------------------------------------------------
             for count = 1 to 10;

               select;
                 when count = 1;
                   workCode = 'AA';
                   work$ = 1.99;
                 when count = 2;
                   workCode = 'AB';
                   work$ = 9.16;
                 when count = 3;
                   workCode = 'AB';
                   work$ = 4.11;
                 when count = 4;
                   workCode = 'AC';
                   work$ = 8.25;
                 when count = 5;
                   workCode = 'AX';
                   work$ = 6.66;
                 when count = 6;
                   workCode = 'AR';
                   work$ = .59;
                 when count = 7;
                   workCode = 'AA';
                   work$ = 16.00;
                 when count = 8;
                   workCode = 'AF';
                   work$ = 11.58;
                 when count = 9;
                   workCode = 'AF';
                   work$ = 2.33;
                 when count = 10;
                   workCode = 'AZ';
                   work$ = 5.77;
               endsl;

               Y = %lookup(workcode:codes);
               if Y = *zeros;
                 x+=1;
                 codes(X) = workcode;
                 codetotal(X) = work$;
               else;
                 codetotal(Y) += work$;
               endif;

             endfor;
                         *inlr = *on;
       //---------------------------------------------------
      /End-Free 

jamie

[raffaeu]

Re: Make various Total during read

Of course you answer is the correct one but i also would like to know how to integrate a SQL statment in a RPG ILE program, so i can show to my boss the power of SQL sintax ...
Maybe she will change her idea ...

[arrow483]

Re: Make various Total during read

C/Exec Sql
C+ SELECT CODE, COUNT(PAX) FROM TABLE GROUP BY CODE
C/End-Exec

[raffaeu]

Re: Make various Total during read

ok this is the answer but how i can manage the loop inside the records i receive from this selection?

[arrow483]

Re: Make various Total during read

Go to www.code400.com

Click on SQLRPGLE

Look for this sample code: SQLRPGLE - Program using SQL to Process data
Posted By: Reynoo Moore


The cursor and fetch are the answer to what you want.

[SG_FTL]

Re: Make various Total during read

i think the cursor has to be defined as 'SCROLL'able in the cursor definition...if your program is trying to use the selected records over and over again, so the pointer can move back and forth.

i will try to get a sample code ..

[raffaeu]

Re: Make various Total during read

i clicked on SQLRPGLE and i see more useful example but today i have my UPS down, if you know today in italy there was and there is a big storm ... I have my AS400 thet every 2 hours shut down ... and so i can't make anything ...

Tomorrow i will try
thanks at all!

[jamief]

Re: Make various Total during read

Hope you stay safe and dry......


take care
jamie