AS/400 physical file

450 pts.
Tags:
AS 400
RPGLE
SQL
Pf 1 file contain records below:
Roll No Subject Marks
1           S1         100
2           S2          80
1           S1          60
2           S2          70
Another file PF2 Contain records below,
Roll No Marks
1           180
2           130
 I need to read pf1 file and write data to pf2 in the above format using RPGLE and also using SQL query so could someone please help me out.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 15  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.
  • TheRealRaven
    Help can be provided. What problem are you running into? Show what you've done so we can see what needs to be done. We can't do your work for you. We can only help you to get past problems.
    25,015 pointsBadges:
    report
  • vivekntt21
    I tried and i am getting the desired output but i want to know whether there is any easy way to do other than below method.
    *********************************************************************** 
    0244.00 ** Subroutine : TESTSR                                                  
    0245.00 ** Description: Writing records to PF2                                  
    0246.00 *********************************************************************** 
    0247.00 C     TestSR        BegSR                                               
    0247.01  *                                                                      
    0248.00 C     *Start        Setll     PF1                                       
    0249.00 C                   Read      PF1                                       
    0250.00 C                   Dow       Not %Eof(PF1)                             
    0251.00  *                                                                      
    0252.00 C                   Eval      Wcnt = 0                                  
    0253.00  *                                                                      
    0254.00 C     PF1RN         Setll     LF1                                       
    0255.00 C     PF1RN         Reade     LF1                                       
    0256.00 C                   Dow       Not %Eof(LF1)                             
    0257.00  *                                                                      
    0258.00 C                   Eval      Wcnt = Wcnt + 1                           
    0260.00 C                   If        PF1RN = '01' and wcnt = 1                 
    0261.00 C                   Eval      WMarks1 = PF1MR                           
    0262.00 C                   Endif                                               
    0263.00  *                                                                      
    0264.00 C                   If        PF1RN = '01' and wcnt = 2                 
    0265.00 C                   Eval      WMarks2 = PF1MR                           
    0266.00 C                   Eval      PF2RN = '01'                              
    0267.00 C                   Eval      PF2MR = Wmarks1 + Wmarks2                 
    0268.00 C                   Write     PF2R                                      
    0269.00 C                   Endif                                               
    0270.00  *                                                                      
    0271.00 C                   If        PF1RN = '02' and wcnt = 1                 
    0272.00 C                   Eval      WMarks3 = PF1MR                           
    0273.00 C                   Endif                                               
    0274.00  *                                                                      
    0275.00 C                   If        PF1RN = '02' and wcnt = 2                 
    0276.00 C                   Eval      WMarks4 = PF1MR                           
    0278.00 C                   Eval      PF2MR = Wmarks3 + Wmarks4                 
    0279.00 C                   Write     PF2R                                      
    0280.00 C                   LeaveSR                                             
    0281.00 C                   Endif                                               
    0282.00  *                                                                      
    0283.00 C     PF1RN         Reade     LF1                                       
    0284.00 C                   Enddo                                               
    0285.00  *                                                                      
    0286.00 C                   Read      PF1                                       
    0287.00 C                   Enddo                                               
    0288.00  *                                                                      
    0289.00 C                   EndSR                                               
    450 pointsBadges:
    report
  • vivekntt21
    I have 2 physical files PF1 and PF2. One logical file lf1 with roll no as key. I have to read PF1 and write the records to PF2 in above mentioned format.
    450 pointsBadges:
    report
  • ToddN2000
    Without knowing the the specifics of the task it sounds like it might be easier to read PF1, chain to PF2 to see if roll no exists. If so add the marks to the existing total if not then write a new record. Give that a try, it would be a shorter program.
    103,870 pointsBadges:
    report
  • Splat
    Is the data in PF1RN one character or two? What's shown above looks to be a single character but your test is for two.
    12,490 pointsBadges:
    report
  • ToddN2000
    Are the "roll no" and "marks" fields numeric or alpha fields? As Splat mentions if alpha you need to test for all positions. If PF1RN is numeric then you don't need quotes around the value when testing it.
    103,870 pointsBadges:
    report
  • vivekntt21
    This is my requirement,
    Pf 1 file contain records below:
    Roll No Subject Marks
    1           S1         100
    2           S2          60
    1           S1          80
    2           S2          70
    Another file PF2 Contain records below,
    Roll No Marks
    1           180
    2           130
     I need to read pf1 file and write data to pf2 in the above format using RPGLE and also using SQL query so could someone please help me out.
    450 pointsBadges:
    report
  • vivekntt21
    Physical file PF1
    0002.00      A          R PF1R                      TEXT('Sample physical +     
    0003.00      A                                           file 1')               
    0005.00      A            PF1RN          2          TEXT('Roll Number')         
    0006.00      A                                      COLHDG('Roll Number')       
    0007.00      A            PF1SB          2          TEXT('Subject')             
    0008.00      A                                      COLHDG('Subject')           
    0009.00      A            PF1MR          3  0       TEXT('Marks')               
    0010.00      A                                      COLHDG('Marks')             
    Physical file PF2 

    0002.00      A          R PF2R                           
    0003.00      A                                                        
    0004.00      A            PF2RN          2          TEXT('Roll Number')         
    0005.00      A                                      COLHDG('Roll Number')       
    0008.00      A            PF2MR          3  0       TEXT('Marks')               
    0009.00      A                                      COLHDG('Marks')    
    Logical file LF1 Keyed with roll no

    0002.00      A          R PF1R                      PFILE(PF1)                  0003.00      A          K PF1RN                                                 
            ****************** End of data **************************************** 
    450 pointsBadges:
    report
  • vivekntt21
    I have PF1 and PF2 and logical file LF1 where roll no is key. Roll no and subject both are 2 characters and marks is 3,0 decimals.
    450 pointsBadges:
    report
  • ToddN2000
    The program is fairly simple in RPGLE, not sure why SQL is needed.
    If this is a class assignment and you are having issues, review the material again or ask the instructor to be clear on the directions if both languages need to be used to solve your assignment. 
    103,870 pointsBadges:
    report
  • vivekntt21
    Yes i want to know any other simple logic to get the output? and in terms of SQL query how can we get the output?
    450 pointsBadges:
    report
  • ToddN2000
    For SQL just do an insert with the data coming from the first file using the SUM and group by attributes. Give it a shot and post your code and any problems you may have if it does not work.
    103,870 pointsBadges:
    report
  • vivekntt21
    Yes got it through SQL query. Now i need simple method in RPGLE to get the output so could you please let me know.
    450 pointsBadges:
    report
  • TheRealRaven
    LF1 seems to have no useful purpose here. And since apparently neither PF1 nor PF2 are keyed, there is no useful sequencing nor random access available. Without adding some attributes, the only way to test PF2 (using only RPG) is to read every PF2 record for each PF1 record to see if a match already exists -- or fall back on the much older "matching records" capability to select non-matching PF1 records. (I can't recall ever needing to do this since it implies a faulty database design.)

    It's not clear why anyone would be teaching "matching records" processing unless some very old programs will be maintained. Any such programs should be rewritten/replaced rather than 'maintained'.
    25,015 pointsBadges:
    report
  • ToddN2000
    I agree with Raven's comments. With those conditions it's like when I first learned RPG back in 1980. Anything that is still using that type of code logic should be re-written as there are a lot of built in functions, and database features that could improve performance.
    103,870 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.

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

Following

Share this item with your network: