How to get a total of amount field in RPGLE code

190 pts.
Tags:
AS400 RPGLE
RPGLE code
I have a table which contain a several types of data for the same customer, I need to get the total amount for each type in RPGLE code.

Software/Hardware used:
Software

Answer Wiki

Thanks. We'll let you know when a new response is added.

Discuss This Question: 36  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Using embedded SQL?
    50,860 pointsBadges:
    report
  • TomLiotta
    If embedded SQL isn't available, a view that presents totals grouped by
    125,585 pointsBadges:
    report
  • TomLiotta
    If embedded SQL isn't available, a view that presents totals grouped by types by customer could be read by RPG.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Well, that was interesting behavior of the editor. Not sure at all what happened.  Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Still favor the SQL route but if it's not available then Following Tom's logic: If data can be accessed in order of customer, type then you're looking at control break logic on customer and type If data can be accessed in order of customer, but type is random then you're looking at an array of types and an array of totals and control break logic at the customer. Phil
    50,860 pointsBadges:
    report
  • TomLiotta
    Still favor the SQL route but if it’s not available Following Tom’s logic: The logic would be the same as for embedded SQL. No control-breaks would be needed. The view would be used to perform the grouping and summarization. It would essentially be the same SQL statement that would be the cursor definition for embedded SQL. The biggest difference would be that you wouldn't need the SQL Dev Kit to be licensed/installed. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    It'd sure be nice if comment editting would work.   Tom
    125,585 pointsBadges:
    report
  • AymanAbbas
    Dear Philp, TomThanks for your kindly replyactually data accessed in order of customer and type is random , i agree with you about the logic but if you have an example about it please adviseThanks and Regards,Ayman
    190 pointsBadges:
    report
  • AymanAbbas
    dear Tom,let me know about it moreThanks
    190 pointsBadges:
    report
  • philpl1jb
    That was really interesting .. my discussion item is now attributed to Tom.  
    50,860 pointsBadges:
    report
  • philpl1jb
    Why don't you show us some code and we'll try to help you achieve your goal?
    50,860 pointsBadges:
    report
  • AymanAbbas
    I will explain more my questionthere is a file which has all customers accounts  ,some of this account types need to be printed with total balance and the rest need to be printed as it is  , i put all the account types which need to be printed with total balance on account level in a new file to chain if the customer has one or more , the printed balance should be with the total of each type levelthere is a part from my code to get ma point easilyC *IN50 DOUEQ *ON C READ SCPFR 50 C IF AYAN = SCAN * C SCACT CHAIN RLACTREC C IF %FOUND C ADD SCBALL $X C MOVE $X $BALL C SCACT DSPLY C $BALL DSPLY C ENDIF
    190 pointsBadges:
    report
  • AymanAbbas
    this part of code displaying the total of all accounts which found in the separated file,which is not right 
    190 pointsBadges:
    report
  • AymanAbbas
    and sorry philp , this is ma first time to use this site , your discussion attributed wronglyThanks and Regards,
    190 pointsBadges:
    report
  • philpl1jb
    C ADD SCBALL $X Well we could get into all kinds of array stuff but since you've got this dedicated file and you're getting a row everytime.  How about adding a column to the file for ActTot opening it for update and add the amount to that field.  Then when you are ready to do the report for a customer part scan the file printing every acount and amount and resetting the amout to zero and updating the file .. so it's ready for the next customer.
    50,860 pointsBadges:
    report
  • TomLiotta
    @Phil:   my discussion item is now...   Actually, your item disappeared entirely. My comment attempted to quote the first couple lines of your previous comment, but things went very bizarre. (Hmmm... apparently there's a hidden way to cause discussions to go away...)   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    i put all the account types which need to be printed with total balance on account level in a new file   Should we assume that RLACTREC is the summary file that has total balances for customers with more than one of whatever it is they can have?   If you're going to read all of the detail records anyway, why not just sum them up when you read them? What is the point of having the second file? It seems that all of the data is already available in SCPFR.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Great, I'm looking forward to making things disappear. If there is a summary file, isn't the problem basically solved. As usual I'm confused.
    50,860 pointsBadges:
    report
  • TomLiotta
    Ah, Phil's comment didn't "disappear". It got moved to become the 'Answer'.   If there is a summary file...   I don't know if there's a "summary" file. Now that I've read back through the discussion, it doesn't look like the comments are there. I thought the OP mentioned that balances were generated into a second file, but now I think that it might just be a file that lists accounts that need balances.   So far, I don't really have a clue what is to be includes in "balances". But I don't know what's in the main file either apparently. The definition of it all is getting less clear.   Tom
    125,585 pointsBadges:
    report
  • AymanAbbas
    with this part of code i reached to get the total balance from the master file directly for the account types needs to be consolidated and get the balance for the rest accountbut still have a problem for passing the consolidated values to the printer filethe consolidated accounts printed incrementally in the report which should be printed once with the last totalC WRITE HDR1 C *IN50 DOUEQ *ON C READ SCPFR 50 C IF AYAN = SCAN * C IF SCAPP = 'RL' * C IF SCACT <> $Y C MOVE SCACT $Y C MOVE SCBALL $X C MOVE $X $TOTBALL C ELSEIF SCACT = $Y C ADD SCBALL $X C MOVE $X $TOTBALL C ENDIF C EXSR PRT1 * C ELSE * C IF SCBALL <> 0 C MOVEL SCBALL $TOTBALL C EXSR PRT1 C ENDIF C ENDIF C C ENDIF C ENDDO  C WRITE HDR1C *IN50 DOUEQ *ON C READ SCPFR 50 C IF AYAN = SCAN * C IF SCAPP = 'RL' * C IF SCACT <> $Y C MOVE SCACT $Y C MOVE SCBALL $X C MOVE $X $TOTBALL C ELSEIF SCACT = $Y C ADD SCBALL $X C MOVE $X $TOTBALL C ENDIF C EXSR PRT1 * C ELSE * C IF SCBALL <> 0 C MOVEL SCBALL $TOTBALL C EXSR PRT1 C ENDIF C ENDIF C C ENDIF C ENDDO C SETON LR * *----------------------------------------------------------------- C PRT1 BEGSR C MOVE $TOTBALL BLBALL C MOVE SCCCY BLCCY C MOVE SCAS BLAS C MOVE SCACT BLACT C WRITE DTL1C ENDSR
    190 pointsBadges:
    report
  • philpl1jb
    “It is always darkest just before the day dawneth.” -  Thomas Fuller   This will be a project for this evening.
    50,860 pointsBadges:
    report
  • TomLiotta
    C *IN50 DOUEQ *ONC READ SCPFR 50...C ENDDOC WRITE HDR1C *IN50 DOUEQ *ONC READ SCPFR 50...   I can't be sure what's going on there. I'd guess that *IN50 is being used to detect EOF for SCPFR at the top, and then again for SCPFR later. Why? If the file reached EOF at the top, it will still be at EOF when you try the second READ.   Besides, you're using a Do-Until operation, and you're setting the condition at the top of the loop. But you don't test the condition anywhere inside the loop. Some (or all) of the statements are going to give incorrect results.   Also, this appears to be RPGLE (RPG IV), but most of the operations are from RPG II or RPG III. You might do better to replace most of the operations with RPGLE instructions.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    That sure came out looking different from what I entered. I'm thinking this editor ought to go back one release.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    I'm sooo confused.  Can you give us samples of what is in the input file (is that SCPFR?) and what the output from this process should look like for that data.  Then we can develop a plan on how to get from one to the other.But not if you insist on using the MOVE and MOVEL operations! 
    50,860 pointsBadges:
    report
  • AymanAbbas
    SCPF data sample SCAB SCAN SCAS SCAPP SCACT SCBALL 0102 080355 001 CA 0 0102 080355 003 CA 188895 0102 080355 002 CF 124880534 0102 080355 461 HB 51566391 0102 080355 462 HT 0 0102 080355 800 H4 0 0102 080355 301 RL JB 0 0102 080355 302 RL JB -103803000 0102 080355 303 RL JB -102713200 0102 080355 304 RL JB -173358900 0102 080355 305 RL JB -110530520 0102 080355 306 RL JB -70754200 0102 080355 307 RL JB -117416400 0102 080355 308 RL JT -3241501627 0102 080355 309 RL JT -1687498018 0102 080355 600 R1 -878410296 0102 080355 310 S5 -10000 0102 080355 701 WN 124887900 what i need to do is printing a report with this data       BALANCE            CCY  TYPE =================================================       1888.95                      EUR  CA  003                                        1248805.34                      EGP  CF  002                                                 515663.91                      EGP  HB  461                                       6785762.20-                     EGP  JB  307 // total of JB                                                49289996.45-                     EGP  JT  309 // total of JT               8784102.96-                     EGP  R1  600                   100.00-                     EGP  S5  310             1248879.00                      EGP  WN  701------------------------------what actually happen      BALANCE            CCY  TYPE =================================================      1888.95                      EUR  CA  003              1248805.34                      EGP  CF  002               515663.91                      EGP  HB  461                     .00                      EGP  JB  301              1038030.00-                     EGP  JB  302            2065162.00-                     EGP  JB  303            3798751.00-                     EGP  JB  304            4904056.20-                     EGP  JB  305            5611598.20-                     EGP  JB  306            6785762.20-                     EGP  JB  307           32415016.27-                     EGP  JT  308           49289996.45-                     EGP  JT  309            8784102.96-                     EGP  R1  600                100.00-                     EGP  S5  310           1248879.00                      EGP  WN  701Hope you get the point and not be confused anymore 
    190 pointsBadges:
    report
  • AymanAbbas
    SCPF data sample SCAB SCAN SCAS SCAPP SCACT SCBALL 0102 080355 001 CA 0 0102 080355 003 CA 188895 0102 080355 002 CF 124880534 0102 080355 461 HB 51566391 0102 080355 462 HT 0 0102 080355 800 H4 0 0102 080355 301 RL JB 0 0102 080355 302 RL JB -103803000 0102 080355 303 RL JB -102713200 0102 080355 304 RL JB -173358900 0102 080355 305 RL JB -110530520 0102 080355 306 RL JB -70754200 0102 080355 307 RL JB -117416400 0102 080355 308 RL JT -3241501627 0102 080355 309 RL JT -1687498018 0102 080355 600 R1 -878410296 0102 080355 310 S5 -10000 0102 080355 701 WN 124887900
    190 pointsBadges:
    report
  • AymanAbbas
    what i need to do is printing a report with this data  BALANCE | CCY | TYPE ================================================= 1888.95 | EUR |CA | 003| 1248805.34 | EGP CF 002 515663.91 | EGP HB 461 6785762.20- | EGP JB 307 49289996.45- | EGP JT 309 8784102.96- | EGP R1 600 100.00- | EGP S5 310 1248879.00 | EGP WN 701
    190 pointsBadges:
    report
  • AymanAbbas
    what actually happen BALANCE | CCY |TYPE ================================================= 1888.95 | EUR CA 003 1248805.34 | EGP CF 002 515663.91 | EGP HB 461 .00 | EGP JB 301 1038030.00- | EGP JB 302 /* 2065162.00- | EGP JB 303 3798751.00- | EGP JB 304 4904056.20- | EGP JB 305 5611598.20- | EGP JB 306 6785762.20- | EGP JB 307 32415016.27- | EGP JT 308 49289996.45- | EGP JT 309 8784102.96- | EGP R1 600 100.00- | EGP S5 310 1248879.00 | EGP WN 701 BALANCE | CCY |TYPE ================================================= 1888.95 | EUR CA 003 1248805.34 | EGP CF 002 515663.91 | EGP HB 461 .00 | EGP JB 301 1038030.00- | EGP JB 302 2065162.00- | EGP JB 303 3798751.00- | EGP JB 304 4904056.20- | EGP JB 305 5611598.20- | EGP JB 306 6785762.20- | EGP JB 307 // the last total amount which need to print 32415016.27- | EGP JT 308 49289996.45- | EGP JT 309 // the last total amount which need to print 8784102.96- | EGP R1 600 100.00- | EGP S5 310 1248879.00 | EGP WN 701 Hope you get the point and not be confused anymore 
    190 pointsBadges:
    report
  • AymanAbbas
    what actually happen BALANCE CCY TYPE ================================================= 1888.95 EUR CA 003 1248805.34 EGP CF 002 515663.91 EGP HB 461 .00 EGP JB 301 1038030.00- EGP JB 302 2065162.00- EGP JB 303 3798751.00- EGP JB 304 4904056.20- EGP JB 305 5611598.20- EGP JB 306 6785762.20- EGP JB 307 // with the last total 32415016.27- EGP JT 308 49289996.45- EGP JT 309 // with the last total 8784102.96- EGP R1 600 100.00- EGP S5 310 1248879.00 EGP WN 701
    190 pointsBadges:
    report
  • philpl1jb
    So, every time you see a new SCACT you'll have a total to print (provided it's not zero) Very easy, should have been part of your logic class. Except I don't know where you're getting the EUR from or why you want to print the 003 or 002 from the last record. There seems to be a little problem with the columns, I assume that 003 comes from SCAPP the others that I want are SCACT SCBALL *-- subroutine to print Balances from file SCPF Read SCPF Eval HoldACT = SCACT Eval HoldSCAPP = SCAPP DOW %not(%eof(SCPF)) *-- now here's where we see if it's time to bail If HoldACT <> SCACT EXSR PrintLN Endif *-- now process current record Eval TotalBall = TotalBall + SCBALL Eval HoldSCAPP = SCAPP Read SCPF Enddo Endsr *.... Begsr PrintLn If TotalBal > 0 Write TotalLn <-- print fields holdACT, HoldSCAPP, TotalBal Endif * --- prepare next group Eval HoldACT = SCACT Eval HoldSCAPP = SCAPP Eval TotalBall = *ZERO Endsr
    50,860 pointsBadges:
    report
  • philpl1jb
    It's unusual for us to provide code .. but there was a great deal of effort on your part.  This code has not been compiled or tested.  Phil 
    50,860 pointsBadges:
    report
  • philpl1jb
    The big challenge here was trying to find something interesting to do with the words total and amount.  Although, I found a few quotes, they didn't appeal to me, so I just went with total and found this quote. "I've had great success being a total idiot." Jerry Lewis
    50,860 pointsBadges:
    report
  • TomLiotta
    A few lines from the suggested code bring a question:* now process current record Eval TotalBall = TotalBall + SCBALL ... *. Begsr PrintLn If TotalBal > 0 Write TotalLn <== print fields holdACT, HoldSCAPP, TotalBal Why would TotalLn include TotalBal in the output? From the supplied examples, what should be printed is SCBALL from the last record in the group. No?   Tom   (And if this comes out looking anything like what I attempted, I'll be pleasantly surprised.)
    125,585 pointsBadges:
    report
  • TomLiotta
    Sigh. So much for useful formatting.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom   6785762.20- | EGP JB 307   Notice the total on this desired outputl Phil
    50,860 pointsBadges:
    report
  • TomLiotta
    Notice the total on this desired outputlAh, yes. Previously his output was showing a "running total". At first it looked like...Well, never mind what it looked like. It's hard to be sure if it's making better sense to me now, but I'll assume it is. In essence, all he wants is a simple summary report.So, again it looks like a simple view with a GROUP BY and a SUM(SCBALL) for each group would provide the values.In pure RPG, with his group fields for the only level break, it would only print the L1 totals to get the report he wants. And your sample code would mostly be fine if full-procedural is the desired method.Everything would be easier to make sense of if formatting would work predictably.Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following