File read

2505 pts.
Tags:
AS/400
I have a file1 with a field account number. Accnum A B C I have another file2 with field Accnum, Security and Qty AccNum Security Qty A X 1 A X 3 A Y 2 A Y 3 Now I need to generate a summary file like this by summing up the quantity for those records with same account number and Qty A X 4 A Y 6 B X 2 C Z 1 Note - File2 can have many account numbers, but I am converned only with A,B and C which are taken from File1. How can I generate a summary file ? I dont want to use SQL, by normal chain and reade how can I achieve this?

Software/Hardware used:
As400

Answer Wiki

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

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.

Discuss This Question: 22  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
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    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.
    2,505 pointsBadges:
    report
  • TomLiotta
    That sounds simple enough. Now, show us the code that you wrote for the programming. -- Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
     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                  
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    If I use a READE after a succesfull chain, I will miss the first record right?
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    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
    2,505 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    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? 
    2,505 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    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. :(    
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    If In SQL, how would the query looks like? I am not familiar with SQL
    2,505 pointsBadges:
    report
  • philpl1jb
    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  
    49,950 pointsBadges:
    report
  • RamvishakRamesh
    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?
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    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.
    2,505 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
     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
    49,950 pointsBadges:
    report
  • philpl1jb
    Wow, how exciting, everytime I enter a discussion box the formating rules seem to change!
    49,950 pointsBadges:
    report
  • philpl1jb
    You shouldn't need to change the code from Oct 19, 2012  9:07 PM (GMT) much to meet your spec of  Oct 20, 2012  6:04 AM (GMT).    References to file 1 are removed setll is removed Reade become read delete row after adding to total need a second hold field for Accnum but it's still a one level control break. A design that removes the source data is risky.  
    49,950 pointsBadges:
    report
  • RamvishakRamesh
    Thanks philip.. I am done.. you are really helpful
    2,505 pointsBadges:
    report
  • philpl1jb
    You're welcome.
    49,950 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