Single page subfile with filter
I have a single page subfile which has two fields, 'Account' and 'description'. The subfile is displayed from File1 which has account(key) but doesn't have description. I am fetching description from another file based on the 'account'. Now in the subfile I want to include a filter or a 'position to'(any of the one will do) based on type and description. But since my subfile is based on file1 which has no description in it, is there any method to filter or position to the subfile?
Software/Hardware used:
AS400
Software/Hardware used:
AS400
ASKED:
January 29, 2013 7:13 AM
UPDATED:
January 29, 2013 12:48 PM
Answer Wiki:
Generally what I do in this situation is add a Function key to allow the user to toggle between which sequence they would like to display the subfile. If they request Acctno, then I use file 1 as input in building the subfile, If they choose to display by description, then I use file 2 as input to build the subfile. File 2 is usually an LF keyed by description over the file that has that field.
When they enter something in the "Position to:" field, I use that value to do a SETLL on what file is being used for the sequence they are currently using.
To see all answers submitted to the Answer Wiki: View Answer History.
But here the problem is am not getting account and description from the same file. I cannot use the file which has the desription to build the subfile because it has many other fields also whihc comes only from the file which has account.
If you are going to ‘Position to’, you should read from a dataset that is sequenced by the ‘Position to’ value. If you can’t do that, then you can read all of the data into your program and sort it.
The data values will be in the same file if you create a JOIN file that join the physical files into one or more logical files. You can create multiple DDS logical files that are indexed by different fields. You can generate a logical file at run-time with OPNQRYF. Or best might be to use dynamic SQL to generate a JOIN view in your program.
With SQL, you can modify the SELECT statement for a CURSOR by adding an ORDER BY clause when the user picks a different ‘Position to’ field. The underlying tables should have indexes that support the various possible ORDER BY conditions.
You can use a blocked FETCH to read blocks that have the same number of rows as your subfile page. If you only care about scrolling the subfile forward from the ‘Position to’ value, a WHERE clause can be added to the SELECT statement.
It doesn’t matter if account and description come from the same physical file. You have to read the file that the ‘Position to’ value is from. Easiest way to do that is to get the fields into the same JOIN logical file or view.
Tom