Read records for a particular period in RPG

2475 pts.
Tags:
AS/400
CL Program
RPG
Hi all! I have a date period in RPG as month and year like 072012 and 022013 both are character fields. I'm passing this fields from OPNQRYF in CL program to RPG. How can I read records from a master for this particular period in RPG? Surey

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.

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

Discuss This Question: 24  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
    I don't know why you'd want to use OPNQRYF at all because it's so old, because there are better ways and because some newer files can't be processed by it. There are probably other reasons not to use it any more. . But if you do use it, you'd open the file from OPNQRYF in your RPG program and read it. . It's not clear at all what you are asking. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Don't understand the OPNQRYF What does the date field in your "master" look like? what type? You will have to make both yyyymm to make sense of the process. Is there a key on the date field in the master? Would you use a
    51,355 pointsBadges:
    report
  • philpl1jb
    Something odd happened there .. discussion posted before I was ready.. rework response OPNQRYF is a bear to write and maintain .. SQL or dynamic SQL in your RPG is generally a better choice. Don’t understand the OPNQRYF .. is it selecting the records from the master to be used? So the RPG program is getting the results of the OPNQRYF. Then why do you need to do more selecting on the date, couldn't this be included in the OPNQRYF? If you're RPG program needs to select on date, here are the issues. What does the date field in your “master” look like? what type? You will have to make both yyyymm to make sense of the process. Is there a key on the date field in the master?
    51,355 pointsBadges:
    report
  • Sureyz
    ok friends, let me leave this OPNQRY and all. In my master the date field(CMNTH) is zoned decimal with 6 length to store month and year like MMYYYY. Now i want to read records from the master for a particular period and Im sending these details to a Printer File. In the mater PF Date field(CMNTH) and other two fields are defined as Key fields.
    2,475 pointsBadges:
    report
  • TomLiotta
    We need to see the key descriptions. Because it's a compound key, the best way might depend on what order the three key fields are in. . Also, it's odd that the key will "store month and year like MMYYYY" instead of 'YYYYMM'. If MM is first, it makes the key unusable for ordering by date. . Tom
    125,585 pointsBadges:
    report
  • Sureyz
    K CMNTH K EMPCDE K DEPCDE K SECCDE This is the order of keys. The key is UNIQUE and no other description about the Key and fields..
    2,475 pointsBadges:
    report
  • TomLiotta
    In RPG, all you should need to do is use SETLL to position before the first record of the CMNTH period that you want; then use READE in a loop until %EOF() is signaled for the file. And if you want a second CMNTH period, go back to SETLL for that new group. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Wow .. CMNTH is mmyyyy and you want to process a range like 072012 and 022013 so your logic might deterine that this range is 072012 - 122012 and a seperate range for 012013 - 022013 As Tom said each range would be processed with a setll and a reade loop But I would consider an SQL cursor ..select xxxx from ffff where (construct to get CMNT in YYYYMM format) between :start_yyyymm and :end_yyyy, order by construct of CMNT in YYYYMM order,
    51,355 pointsBadges:
    report
  • svankipu
    Hi Sureyz, let's say you receive your two dates(viz., 072012 and 022013) into two variables(DAT1 & DAT2) in your RPG. now use a DOW-Loop & a subsequent READ on the file till the condition satisfies as shown below.... DOW (CMNTH >= DAT1 and CMNTH <= Dat2) -----------------|_ ***do all those processing that are required here. -----------------| READ FILE ENDDO
    620 pointsBadges:
    report
  • philpl1jb
    CMNTH >= DAT1 and CMNTH = 7 and <= 2)
    51,355 pointsBadges:
    report
  • philpl1jb
    That is not what I wrote .. these char fields contains '072012' and '022013' .. looks like dates but they are character fields. .. the between logic would be ge 07 and le 02 .. no rows would be selected. Must be processed as one loop per year.
    51,355 pointsBadges:
    report
  • TomLiotta
    Yes, with MM in the key field in front of YYYY, the file keys can't be effectively used to process a range of dates if YYYY values are from two different years. The field is improperly defined to act as a useful key. It needs to be YYYYMM instead of MMYYYY. . If we assume that the program receives FROM as 072012 and TO as 022013, the program could calculate a series of temporary ranges based on years. The first range would be 072012 through 122012, and the next range would 012013 through 022013. By looping through month values to a maximum of 12 and starting at a minimum of 01, logic could process records one month at a time. . But it's almost a useless key definition. . OPNQRYF could be used to reformat the field and to provide a useful. It's not clear why you'd do it that way though. If the key can't be used, it'd be better simply to redefine the file and fix the data. Why have a key at all if it always has to be redefined by the programming that uses it? . SQL would be better than OPNQRYF, but the same question comes up. Why have a key that can't be used without redefinition? It's just an index that the system has to maintain without being able to use it. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    “This must be Thursday,' said Arthur to himself, sinking low over his beer. 'I never could get the hang of Thursdays.” ― Douglas Adams, The Hitchhiker's Guide to the Galaxy
    51,355 pointsBadges:
    report
  • philpl1jb
    No, those ranges won't work, as Tom pointed out, it has to be processed one month at a time 072012 082012 092012,,
    51,355 pointsBadges:
    report
  • MDratwa
    You can also take the time to create a logical file using the "SST" to break the character date field into 2 fields (month & year). The new fields can be made key of year & month. This way the physical file is not change (if there is a reason for not changing the physical file). I am sure there is a way using SQL to create a view breaking down the date field to year & month. The logical file is a quick way which may not be the best but the task is completed (until this is a production request).
    785 pointsBadges:
    report
  • Sureyz
    Hi all after some self analysis i have modified the code a little bit. C/EXEC SQL C+ SELECT SUM(PRAMT, SALARY) INTO :PRAM FROM MASTER1 WHERE CMNTH BETWEEN C+ :FRMDAT AND :TODAT GROUP BY EMPNO,CMNTH ORDER BY EMPNO C/END-EXEC This is my sql query in sqlrpgle program.. here each EMPNO will have many entries for the same month in the master1. i need to add all PRAMT,SALARY for all EMPNO individually and also for a particular period. How can i write the query .. ? Sureyz
    2,475 pointsBadges:
    report
  • TomLiotta
    That SQL would be okay under a few conditions. Either CMNTH needs to be fixed in the table to be YYYYMM instead of MMYYYY or MASTER1 needs to be a VIEW (or regular LF) that defines CMNTH as YYYYMM. You can't use MMYYYY as the form for a selection by period unless you only have a single year in the table or you don't care if the periods are out of order and include periods outside of the desired range. (Period 012013 is going to sort before 122012 because '01' comes before '12', and 062011 will come between 122012 and 012013 because '06' is between '01' and '12'.) :FRMDAT and :TODAT also need to be YYYYMM before the query runs. . It looks as if you should have [ SELECT SUM(PRAMT), SUM(SALARY)... ] instead of [ SELECT SUM(PRAMT, SALARY) ]. . Also, you can FETCH in a loop totaling the values. When EMPNO changes, you have the total for that EMPNO. Each row already provides an EMPNO's total by period. . Tom
    125,585 pointsBadges:
    report
  • Sureyz
    Hi yes are right Mr.Tom. can you tell me how can i reverse the field(CMNTH) like YYYYMM in LF which is MMYYYY in the MASTER1.? In MASTER1 the field is Zoned Decimal... and also i need to know, how can we view or display the query while we doing the debug in SQLRPGLE.?? Surey.
    2,475 pointsBadges:
    report
  • Sureyz
    It seems to be difficult to me to create an LF with reversing the MMYYYY.. Bcoz badly I'm not aware the logic. I though it will better to create a view in SQLRPGLE with reverse the CMNTH and im doing it. Here also im getting some problem. See problem is everywhere :P when im doing CONCAT(SUBSTR(CMNTH,1,2),SUBSTR(PCMNTH,3,4)). but its giving me wrong figures, obvious bcoz the value is not stored as for ex 092012 , instead of this it is stored as 92012. Can anybody help me how can i move forward ?
    2,475 pointsBadges:
    report
  • TomLiotta
    CONCAT(SUBSTR(CMNTH,1,2),SUBSTR(PCMNTH,3,4))
    .
    That's backwards. It needs to be:
    CONCAT(SUBSTR(CMNTH,3,4),SUBSTR(PCMNTH,1,2))
    .
    Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    ...how can we view or display the query while we doing the debug in SQLRPGLE.?
    .
    It's not clear what that question means. It's not a dynamic query statement, so you don't need to view it in debug. It's exactly the same as it is in the source. But you view source in debug by compiling with the DBGVIEW(*SOURCE) or DBGVIEW(*ALL) option.
    .
    Best is simply to put the debug option in the H-specs.
    .
    Tom
    125,585 pointsBadges:
    report
  • Sureyz
    No.. Mr.Tom. Again a mess here.. CONCAT showing like 012 92... I dont thing this SQL and ViEW will work for this particular problem in RPGLE program.. My requirement is this.. Earlier it was to retrieve for only one month, they had used OPNQRY file in CL and passed the value to RPG program. then the code was. *HIVAL SETLL MASTER1 READP MASTER1 --------------------------------- -------------------------------- something like this.. But now we want to retrieve the value for a particular period. Please help me to write the code in RPGLE itself .. . Surey..
    2,475 pointsBadges:
    report
  • TomLiotta
    It's a zoned-decimal field, so it needs special help. I'm not sure why anyone would use such a field because it's easy to forget how it needs to be handled: CONCAT(SUBSTR(DIGITS(CMNTH),3,4),SUBSTR(DIGITS(PCMNTH),1,2)) . And is that second variable supposed to be PCMNTH instead of CMNTH? . Tom
    125,585 pointsBadges:
    report
  • Sureyz
    Finally got the output exactly .. Thanks Team. Sureyz..
    2,475 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