Using embedded SQL?

If embedded SQL isn’t available, a view that presents totals grouped by

If embedded SQL isn’t available, a view that presents totals grouped by types by customer could be read by RPG.
 
Tom

Well, that was interesting behavior of the editor. Not sure at all what happened.  Tom

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

It’d sure be nice if comment editting would work.
 
Tom

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

dear Tom,let me know about it moreThanks

That was really interesting .. my discussion item is now attributed to Tom.
 

Why don’t you show us some code and we’ll try to help you achieve your goal?

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

this part of code displaying the total of all accounts which found in the separated file,which is not right 

and sorry philp , this is ma first time to use this site , your discussion attributed wronglyThanks and Regards,

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.

@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

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

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.

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

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

“It is always darkest just before the day dawneth.” -  Thomas Fuller
 
This will be a project for this evening.

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

That sure came out looking different from what I entered. I’m thinking this editor ought to go back one release.
 
Tom

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! 

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 

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

49289996.45- | EGP JT 309

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 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 

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

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

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 

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

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.)

Sigh. So much for useful formatting.
 
Tom

Tom
 
6785762.20- | EGP JB 307
 
Notice the total on this desired outputl
Phil

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