Midrange News for the IBM i Community


Posted by: John Tremper
LF with date key in descending order
has no ratings.
Published: 04 Jan 2013
Revised: 23 Jan 2013 - 1728 days ago
Last viewed on: 16 Oct 2017 (5723 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

LF with date key in descending order Published by: John Tremper on 04 Jan 2013 view comments(13)

I'm worried I may be stuck on a rookie mistake, but here goes.  I have a physical file with the following keys:

K INSCO                             

K EFFDATE                           

K ACTION                            

K DATEADDED                         

K TIMEADDED                         

K DATECHANGE                        

K TIMECHANGE                        

and a logical on that physical that looks like this:

K INSCO                                                    

K EFFDATE                   DESCEND                        

O ACTION                    CMP(EQ 'B')                    

My interactive RPG program has this key list:

fsKey2        klist                                      

              kfld                    insco              

              kfld                    effect_date        

And the read looks like this:

              eval      effect_date = *hival                         

fsKey2        chain     fsstandl2                                    

              readp     fsstandl2                              95    

              if        not %found(fsstandl2)                        

              eval      errorLine = 'No Rates Found'                 

              else                                                   

              clear                   errorLine                      

              endif                  

The file contains 2 records and the read should be finding them, but it isn't.  I know I am missing something simple, but I have been looking at this chunk for 2 days with no positive results.

 

Thanks in advance!                              

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 12 days 9 hours 34 minutes ago

Try this code:

               

eval effect_date = *hival
fsKey2 setll fsstandl2
insco reade fsstandl2
if not %eof(fsstandl2)
eval errorLine = 'No Rates Found'
else
clear errorLine
endif

 

                

Posted by: jtremper
Premium member *
Comment on: LF with date key in descending order
Posted: 4 years 9 months 12 days 9 hours 2 minutes ago

That seems to have done it.  The chain should have been setll and the readp should have been reade.  Now I just need to incorporate this in to my loop and I should be good.  Thanks for your help Brian!

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 12 days 8 hours 41 minutes ago
Edited: Fri, 04 Jan, 2013 at 16:06:53 (1747 days ago)

I just noticed a small logic error.

The line:  if not %eof(fsstandl2)

should be:   if %eof(fsstandl2)

 

Glad I could help.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 9 days 16 hours 29 minutes ago

chain requires an exact hit. setll places the file pointer to the match, or just before the next up in sequence. That is why in this example chain doesn't work (often), but setll always works.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: LF with date key in descending order
Posted: 4 years 9 months 9 days 14 hours 48 minutes ago

The *HIVAL symbol along with your opcodes is what is wrong.

Use this to read the first record:

setll *start myfile;
read myfile; 

  

Use this to read the last record in the file/key sequence:

setll *end myfile;
readp myfile;

 

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 9 days 13 hours 33 minutes ago

It looks like John wants to retrieve the record with the highest effective date within a subset of records in the file limited to whatever the value of the INSCO field is.  If that's the case, using *START would not work since it would position you to the beginning of the file, and you want to be at the beginning of the group equal to the value of INSCO.

Posted by: jtremper
Premium member *
Comment on: LF with date key in descending order
Posted: 4 years 9 months 9 days 11 hours 15 minutes ago

Yes Brian, you are correct.  The insco field is a specific field, and I am wanting to retrieve the records in descending date order for the insco.

Posted by: jtremper
Premium member *
Comment on: LF with date key in descending order
Posted: 4 years 9 months 8 days 13 hours 57 minutes ago

For some reason this lf with a descending date access path is a real thorn in my side.  In the same program when I am loading my dspf it isn't stopping when it reaches the end of the set value for the insco field.  So, I have several records where **** is the insco and several records where ADCM is the insco.  But, when the dspf loads both **** and ADCM are showing up even if insco is set to ****.  Here is how things are set:

fsKey2        klist                                      

              kfld                    insco              

              kfld                    effect_date        

              eval      effect_date = *hival                             

fsKey2        setll     fsstandl2                                        

              reade     fsstandl2                              95        

              dow       not %eof(fsstandl2)                                

              eval      sflrcd = rec# + 1                                  

              do        13                                                 

              eval      rec# = rec# + 1                                    

              write     sfData                                             

              read      fsstandl2                              95          

              if        *in95 = *on                                        

              leave                                                        

              endif                                                        

              enddo                                                        

              enddo                                                        

 

Once again, thanks in advance!!!

Posted by: DaleB
Premium member *
Reading, PA
Comment on: LF with date key in descending order
Posted: 4 years 9 months 8 days 13 hours 23 minutes ago
Edited: Tue, 08 Jan, 2013 at 11:25:43 (1743 days ago)

Sounds like your nested read fsstandl2 should be a reade on insco as the only key. By using read, you're accepting any record that comes next, whether it's for the same insco or not. Here's an excerpt...

     C                   Write     sfData
     C     fsKey3        KList
     C                   KFld                    insco
     C     fsKey3        ReadE     fsstandl2                              95
     C                   If        %EOF
     C                   Leave
     C                   EndIf

 

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 8 days 12 hours 24 minutes ago
Edited: Tue, 08 Jan, 2013 at 12:26:47 (1743 days ago)

If I may expand on Dale's example (I took out the uneccessary indicator and eliminated the extra key list):

 

fsKey2 klist
       kfld  insco
       kfld  effect_date

       eval  effect_date = *hival

fsKey2 setll fsstandl2
insco  reade fsstandl2
       dow   not %eof(fsstandl2)
       eval  sflrcd = sflrcd + 1
       write sfData
insco  reade fsstandl2
       enddo

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: LF with date key in descending order
Posted: 4 years 9 months 8 days 11 hours 30 minutes ago
Edited: Tue, 08 Jan, 2013 at 13:17:50 (1743 days ago)

D'oh! Yes, if you only have one field in a KList, just use it directly. I've been using free format long enough that I overlooked this. However...

I intentionally left the *IN95 on the READE. If he's using 95 for something in the DSPF DDS, such as SFLEND, or even somewhere else in his program, he still needs it.

I think the loop still isn't quite correct, though. He originally had the Do 13, which would quit after no more than 13 reads, unless you hit EOF first. But if he reached 13 reads without EOF, the outer loop would keep going. The inner loop does seem unnecessary, but I wonder if he was trying to load only 13 rows?

Also, it seems like rec# is the SFILE() from the F-spec, and sflrcd could be SFLRCDNBR from the DDS. In which case you still need to do something with both of them in or near this loop.

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: LF with date key in descending order
Posted: 4 years 9 months 8 days 6 hours 40 minutes ago

Hi Dale,

You make some valid points.  I was just trying to show the basic concept of writing subfile records from within a READE loop rather than trying to guess at what John's example code needed to accomplish, and hopefully he can adapt it to his situation. 

Posted by: jtremper
Premium member *
Comment on: LF with date key in descending order
Posted: 4 years 9 months 7 days 15 hours 59 minutes ago

Thanks all, things are working great now.  I appreciate the help.  I have been RPG coding for a number of years, but this lf with descending dates was causing me trouble.

 

Thanks again