It’d be easy to say how to do it with SQL.
 
I dont want to use SQL, by normal chain and reade how can I achieve this?
 
But there isn’t enough information to know how to do it using CHAIN and READE. SQL is a better choice.
 
I am converned only with A,B and C
 
How will your program know those are the only accounts you want? Are those the only accounts that exist in FILE1? If they are, then the READ instruction could be used to read FILE1.
 
Every time you read from FILE1, you’ll probably use Accnum to CHAIN to FILE2 (assuming that FILE2 allows it). If the CHAIN succeeds, you’d then use READE in a loop to get all FILE2 records that match Accnum. You’d add the Qty to a subtotal, and repeat the loop. When that Accnum runs out, you exit that loop and output your summary record.
 
Then go to READ the next FILE1 record and start it over again.
 
At least, that’s probably how it would go. It’s a pretty standard programming sequence. You should have few problems putting source together and showing us what prolems you run into. Until general source is shown or a specific problem question is asked, that’s about all that can be said.
 
Tom

I need all accounts from File1. My file2 has account number, security and quantity. For account number A there can be security X with Qty1 again for same account security X with Qty 3. In the summary file, I need to show Account A , Security X and Qty 4(3 + 1). When ever the security changes for each account, I have to sum up the Qty.

That sounds simple enough. Now, show us the code that you wrote for the programming. — Tom

 K01           KLIST                                         KFLD                    ACC1  
 
C     *START        SETLL     FILE1                         C                   READ      FILE1                         C                   DOW       NOT%EOF(FILE1)                C     K01           SetLL     File2                         C     K01           ReadE     File2                         C                   Eval      Wrk_Sec = Sec2                C                   Eval      Wrk_Qty = Qty2                C                   DoW       Not %Eof(File2)               C                   If        Wrk_Sec = Sec2                C                   EVAL      WRK_QTY = WRK_QTY + QTY2      C                   Else                                    C                   EVAL      ACC3 = ACC1                   C                   EVAL      QTY3 = WRK_QTY                C                   EVAL      SEC3 = WRK_SEC          
C                   WRITE     RCDFMT3             C                                                 C                   EndIf                         C                   Eval      Wrk_Sec = Sec2      C                   Eval      Wrk_Qty = Qty2      C     K01           READE     FILE2               C                   EndDo                                                                           C                   READ      FILE1              
C                   ENDDO                             C                   EVAL      *INLR = *ON                  

If I use a READE after a succesfull chain, I will miss the first record right?

I changed the logic to use a chain, then a Setll and then a ReadE in a loop. I got the records, but when the security changes, it comes out of the READE loop and then it reads for the second accountt numbr, so If I have Acc num A and Sec X, it counts total. But not listing Acc A and Sec Y. as it comes out of the reade loop and go for second record B. Please help

I will miss the first record right?
 
No, the CHAIN not only positions to a record (if a match exists), it also reads that record. The ‘first’ is already loaded in the fields in your program. When you do the READE after that, it reads the next record the ‘E’quals the key value. That would be the second record in the group. If there is no second record, it signals end of the group.
 
Tom

Every time you read from FILE1, you’ll probably use Accnum to CHAIN to FILE2 (assuming that FILE2 allows it). If the CHAIN succeeds, you’d then use READE in a loop to get all FILE2 records that match Accnum. You’d add the Qty to a subtotal, and repeat the loop. When that Accnum runs out, you exit that loop and output your summary record.
Here a READE after a Chain is going to read the second reocrd and so the first record’s Quantity wont get added up right?  Can you please give the code for this? 

…it comes out of the reade loop and go for second record B. Please help
 
This is where you need to learn how to use the STRDBG command.
 
You code is actually very close. Not bad. But you need to watch how it runs in debug to let it show you exactly what’s happening.
 
Tom

Can you please give the code for this?
 
There is no code. It’s just a CHAIN. It’s logically the same as SETLL + READE. There are technical details that make them different which is why study of the documentation is needed along with experience.
 
Tom

I cannot do with Just a chain, as my first file has only account and my second file has account as well as Security as the key.
I have adopted this logic
First I will read my file1 from begining, then DoW not %Eof(File1)
ReadE on file2 with Account number as the Key. Will save security value to a work field. Then DoW not%Eof(File2), check whether prev security value = current security value, If so, add the Quantity else, write to the summary record. In the else part, make Quantity = current quantity. Save current security value and the reade loop continues. The prob I face here was, It was not writing the summary record for the last record. For that I have initialized a flag and made the flag value to ‘Y’ when it enters the DoW loop. The code seems to be complex. :(  
 

If In SQL, how would the query looks like? I am not familiar with SQL

Ah the code window doesn’t seem to work…
 
Read file1
 
DOW not %EOF(file1)
 
Setll (AccNum) File2
 
Reade (AccNum) File2
 
Eval HoldSecurity = Security
 
Dow Not %EOF(File2)
 
If HoldSecurity<> Security
 
  EXSR ProcSecBrk
 
Endif
 
Eval Total = Total + Qty
 
Reade (AccNum) File2
 
ENDDO
 
  EXSR ProcSecBrk // get the last set of current accnum
 
Read File1
 
Enddo
 
*INLR = *on
 
ProcSecBrk   BEGSR
 
Write Line <– or whatever but use HoldSecurity value and total
 
Total = 0
 
Eval HoldSecurity = Security
 
Endsr
 

philpl1jb, thanks for that. But suppose all the accounts has only one quantity correspondin to it. So when prev value not equal to current value, it will page break and try to page berak. As per your logic, it will write the total value which is 0. how to handle this?

Leave all these things, If I have a file which has multiple quantity corresponds to the same account and Security, Is it possible to write an embedded SQL statement to sum up the quantity ? FileA
Acc Sec Qty
A x 2
A x 3
A y 1
A y 2

Would like to make it as FileA
Acc Sec Qty
A x 5
A y 3 Cannot write to another file, instead in the same file I need to sum up the Qty.

I cannot do with Just a chain, as my first file has only account and my second file has account as well as Security as the key.
 
Why is that a problem? CHAIN can work with the first key field, the first two key fields or as many of the beginning fields as you have. In this case, the account is the only one you want to use and it’s all you need.
 
Did you read the documentation?
 
Tom

I am not familiar with SQL
 
Then you need to learn SQL basics before discussing it. And learn how to use the STRDBG command. We can’t debug all of a program’s problems through a web site. If you have problems with STRDBG, we can help with problems. But we can’t do work for you. We can help, but help is all that can be done.
 
Tom

 I’m done.  The code I’ve provided fulfilled your spec.  “But suppose all the accounts has only one quantity correspondin to it. So when prev value not equal to current value, it will page break and try to page berak. As per your logic, it will write the total value which is 0. how to handle this?” If there is only one row in file2 for a accnum in file1.  This code will work properly.  Please review it again.   However, if there are no rows in file 2 for a accnum in file 1 .. say accnum =’D’ then it will print something you may not want.  This case wasn’t in your spec but I’m confident that you can find ways to keep from printing when the total = 0 Phil

Wow, how exciting, everytime I enter a discussion box the formating rules seem to change!

Thanks philip.. I am done.. you are really helpful

You’re welcome.