How to position a subfile using SQL?

[tiarac]

Hello,

I displayed a list depeding on a criteria. I would like to allow the user to position to specific data inside the same list displayed and be able to rollup and down inside the same list displayed after they position to. A good sample of what I am trying to do is "Work with Members using PDM". I would like to use embedded sql into my RPGLE program. I don't want to load all records into the subfile.

Anyone knows how to do this or have a good sample?

Thank you in advance.

[jamief]

A bit more definition

Tiarac,


There are various SQLRPGLE examples on the site.

www.code400.com/viewsamples.php?lang_id=3

     C/EXEC SQL
     C+ DECLARE A CURSOR FOR
     C+  SELECT *
     C+  FROM SR3ISD
     C+  WHERE IDIDAT BETWEEN :@FDATE AND :@TDATE AND
     C+        IDPGRP BETWEEN :@PRDG1 AND :@PRDG2 AND
     C+        IDSALE <=      :@HIGH_MI_DSM AND
     C+        IDFOCC <> 'Y'
     C/END-EXEC

What this "hunk" of code does is allow for variable..Variables
the program defined variables for example @FDATE & @TDATE
are dates which can come from the control record of the subfile.

So you would clear the subfile then reload the subfile using
the above SQL as an example.

If you wish to load only a page at a time then place code similar to the above in a subroutine and then put this code in a do loop
for example do 10

Do 10
exsr $GET
eval RRN = (RRN +1)
move your file field values to subfile dsiplay fields
write subfile
enddo

     C     GET           BEGSR

      * Get invoice detail records using dealer cursor

     C/EXEC SQL
     C+   FETCH A INTO :INVDETL
     C/END-EXEC

     C                   ENDSR

Oh!! INVDETL is a data area see below:

DINVDETL        E DS                  EXTNAME(SROISDPL)

Let me know if I can be of further help.
Thanks,

Jimmy

[tiarac]

Jimmy thank you for your help.

I have that piece of code in my program -- what I am not sure how to code is to position the list all ready displayed when the user enter something in the field to position to the list. And once they position to, how to rollup and down where they can see the previously loaded records before they position to.

[jamief]

I think this is what your looking for

Okay.......Lets forget the SQL part and focus on the subfile.

First you are going to have to load only one page at a time and control the pageup/pagedown via your program.

     A          R SUB01CTL                  SFLCTL(SUB01)
     A*
     A                                      SFLSIZ(0010)
     A                                      SFLPAG(0010)
     A                                      CF06(06 'Add')
     A                                      CF12(12 'Cancel')
     A N89                                  PAGEDOWN(33)
     A N90                                  PAGEUP(34)
     A                                      RTNCSRLOC(&#REC &#FLD)
     A                                      OVERLAY
     A                                      SFLCSRRRN(&WHERE01)
     A  50                                  SFLDSP
     A  51                                  SFLDSPCTL
     A  52                                  SFLCLR
     A  89                                  SFLEND(*MORE)
     A            SCRRN01        4S 0H      SFLRCDNBR(CURSOR)
     A            #REC          10A  H
     A            #FLD          10A  H
     A            WHERE01        5S 0H

**Notice SFLSIZ & SFLPAG are the same - plus the pageup/pagedown keys are defined.

You must create key lists back to the subfile

so write the the physical file key(s) to a pageup keylists (RRN1)
and a pagedown keylist (RRN10)

Then create two subroutines --- one $pageup and a second $pagedown


$pageup will use setll on your file by the pageup key on the physical file. Then readp 11 times........then i KNOW IT SOUNDS NUTTY but read 10.

$pagedown will use setgt on your file by pagedown keylist and read 10 timeswritting the subfile.


between each pageup/pagedown you have to clear the subfile.



This will allow you to use control record fields to setll with your file and start loading the subfile by these values...Plus you can use the pageup/pagedown keylist (defined above) to roll back and roll forward.

*****************************************

There is no simple way to do this...but if you write the records including the RRN number to a datastructure or external data table you can then do a lookup or chain or setll to fine the record
the user is searching for.

Then retrieve the RRN from the data structure or physical file and
use a combination of this to position the subfile.

     FBACKUP02D CF   E             WORKSTN INFDS(INFDS)
     F                                     SFILE(SUB01:RRN1)
      *
     C                   If        CURREC <> *Zeros
     C                   Eval      RRN1    =  CURREC
     C                   Eval      SCRRN   =  CURREC
     C                   Endif

You can use the RRN from the to chain to the subfile, then
z-add RRN to CURREC and update the subfile.


Jimmy

[tiarac]

I wanted to write the subfile using embedded sql. I never done it with sql. Per your reply I see that it can not be done, so I when back to the old way of doing it. Jus plain all rpgIV.