AS/400:- How to select records to be displayed in subfile

625 pts.
Dear All, Thanks to all your inputs. As some of you suggested, Earlier I have used SQLRPG & achieved the result by dynamically building the SQL Query string. but now they want to scrap the SQL because they say it's a performance issue(i.e. it slows down the system & a over head on it). Now what is the other alternative? pls. let me know. ---------------- I have a requirement like this:- 1) I have a screen with some "FROM" and "TO" fields(see below:) -------------------------------------------------------------------------------------------------------------- Select: From To ------------------------------ ----------- ----------- Fax Referenece Number: XXXXXX XXXXXX Order date: XX/XX/XX XX/XX/XX Contract number: PKXXXXX PKXXXXX Item: XXXXXXXXX XXXXXXXXX Warehouse: XXX XXX Vendor: XXXXXX XXXXXX F3 - Exit Press enter ---------------------------------------------------------------------------------------------------------------- all records satisfying the above range, have to be selected & displayed in a subfile for further process. (The user can enter all fields, some fields or only one field etc.,(but atleast one field is required)). Thanks in advance, Svanky

Answer Wiki

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


I guess that means you’re back to using reads, you’ll need to choose the best possible keys for your file and use CHAIN or SETLL to get your start position in the file, then loop doing a READ of your database file and checking whether your retrieved record matches your selection criteria or not and WRITE to the subfile record if your criteria matches. Something like this :-

Yourkey SETLL
Yourkey READE Yourfile (or just READ, depending on your key)
EndOfFile DOWEQ *Off
FaXRefNrFrom IFLE FaxRefNr
FaxRefNrTo IFGE FaxRefNr
OrderDateFrom IFLE OrderDate
OrderDateFrom OREQ *ZEROS
WRITE YourSubfileRecord
Yourkey READE Yourfile (or just READ, depending on your key)

This may end up causing your program to read the whole file from start to end, I suggest you keep a copy of your SQL version so you can compare the performance of the two methods.

You can probably do this easier in free format, but I seem to recall you were using non-ILE RPG.


Martin Gilbert.

Discuss This Question: 1  Reply

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.
  • Sloopy
    The sort of selection you want is certainly more suited to SQL than to native database processing. And in the screen programs I have done, I have found either no great speed difference between 'ordinary' RPG selection and embedded SQL selection, or else the SQL selection was much faster. However, there are some ways to speed things up. In SLQRPG, NEVER select from logical files. SQL does not like logical files. See this article to find out why: See THIS article to see how to make sure that your iSeries SQL will not use logical files : In addition, coding multiple conditioned SQL SELECT statements in your program can also speed things up, because you can then use the SELECT statement that best fits the selections the user has made from your screen. So, for the cases where the user only fills in a single range, there is a SELECT just for that single range. But there must always be a SELECT for the worst-case, where multiple ranges and single values have been selected. Now to 'ordinary' RPG processing. I am assuming here that all your ranges are on data held in the same physical file. First, look at the logical files you have to play with. You can specify all those that allow you to select the ranges of data required in your F-specs, and of course they must have different record format names in the code. (Create a D-spec externally-described data structure for the file. It will be filled in by any of the record formats when you do a read from any of the logicals, because they all have the same field names. You don't really need it, but if your screen fields are based on a similar extrenally-described structure with a renamed prefix, then just one EVAL will move the data from the file into the structure for the screen, so saving some time.) Have a subroutine which examines the user selections, and which will decide which logical file to use. So, if the user selects the range FAX REFERENCE NUMBER, your routine will decide to use the logical keyed on fax reference number . The object is to reduce the number of database reads. However, there may be issues regarding the order the user expects the data to be shown in the subfile. Your subroutine will have set a multi-valued flag that will control a Select group to SETLL the selected logical using the FROM range value, and then in a read loop to control a Select group to Read the logical. You READ the range for the key. Test the key value , and when it is greater than the TO range value leave the loop. If the user entered just one value (meaning 'just this value'), then set the TO value = the FROM value. Inside the loop, perform sub-selection, where the user has entered additional selection values not covered by the logical you used. So, if the user entered a range of Fax Reference Numbers and a single Order Date, and you don't have a logical based on order date, you will read the logical based on Fax Ref No for the range, and within the loop have: Select When ScreenOrderDateFrom = *Zero When ScreenOrderDateTo = *Zero and FileOrderDate <> ScreenOrderDateFrom Iter When FileOrderDate < ScreenOrderDateFrom or FileOrderDate > ScreenOrderDateTo Iter EndSl The Iter is the fastest way to loop round for the next record. Of course, you'll also have a Select group that covers the range your logical is keyed on, just because the read loop could be reading from any of the logicals; but this Select group will never make an Iter. Another thing to look at is, how many records do you expect to find? If it's a lot, then think about loading the subfile a page at a time, so the user gets one page as soon as it is filled, and the program only looks for the next page of data when the user pressed Roll Up (Page Down). Otherwise you may spend a lot of time loading a lot of records, and the user will get bored and make a cup of tea, spill it on his keyboard, and you will get the blame. Regards, Sloopy
    2,195 pointsBadges:

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.


Share this item with your network: