Database
First of all, any DBA will tell you, if you are creating this from scratch, don't use DDS to define your files. In the long run, you will be better off if you define them with SQL, something like this:
CREATE TABLE USERMAST
(UserID char(7),
UserName char(25)
);
LABEL ON TABLE USERMAST is 'User Master Table';
CREATE TABLE WORKPERIOD
(UserID char(7),
WorkDay date,
StartTime time,
EndTime time
);
LABEL ON TABLE WORKPERIOD is 'User Work Periods';
Note that this is using actual data and time fields, not numeric fields. This makes it easier to manipulate as needed. When you run SQL, you can specify your preference for date and time formats, such as *ISO, *DMY, or *EUR. You can think of it as being stored in *ISO format, but given to you in whichever format you wish.
Whether your files (tables) have been defined in DDS or SQL, you should still use embedded SQL to read the data in your program. At first, it may seem more complex than native I/O. But as you learn about it, you will see it is more powerful, easier, faster, and flexible.
Processing
The basic concept for embedding the SQL is not actually that complex. Your program could something like this in free-format ILE RPG (assuming that is what you are using):
EXEC SQL DECLARE CURSOR c1 FOR your-select-statement;
EXEC SQL OPEN c1;
do while SQLSTATUS is ok;
EXEC SQL FETCH FROM c1 INTO :var1, :var2, ... ;
if SQLSTATUS is ok;
// process data
endif;
enddo;
EXEC SQL CLOSE c1;
Check for end of data, or other issues, by checking the first two characters of SQLSTATE. '00' means everything is OK, '01' is a warning (probably not ok), everything else is an error. (This is easier than using SQLCODE.)
You put a SELECT statement (more specifically a full-select) in your DECLARE CURSOR statement, that determines what the system will give you when you FETCH from the cursor. The SELECT can do a lot of powerful things. Here it will get information from both tables and summarize rows for you. If you were only summarizing, it might look like this:
SELECT userid, workday, min(starttime) as firsttime, max(endtime) as lasttime
FROM workperiod
GROUP BY userid, workday
If you were just joining data from both tables, it might look like this:
SELECT u.userid, u.username, p.workday, p.starttime, p.endtime
FROM UserMast as u
JOIN WorkPeriod as p on u.userid = p.userid
The prefered way to put these together is like this:
WITH s as
( SELECT userid, workday, min(starttime) as firsttime, max(endtime) as lasttime
FROM workperiod
GROUP BY userid, workday
)
SELECT u.userid, u.username, s.workday, s.firsttime, p.lasttime
FROM UserMast as u
JOIN WorkPeriod as p on u.userid = s.userid
ORDER BY workday descending, username
FOR INPUT ONLY
For more information on using SQL to access the database look in the IBM i Information Center. Look under Database / Reference. For information on Display files look in the Information Center under Programming / DDS / DDS for Display files,
Display File
As far as your display file goes, you will need to define at least two record formats for each subfile screen -- a subfile record format, and a subfile control record format.
The subfile record is for a row in the list. It should have the SFL keyword.
The subfile control record manages the screen. It normally contains your screen headings and column headings for the subfile. It should have SFLCTL(yoursubfilename), SFLSIZ(subfile-rows), SFLPAG(rows-per-page), nn SFLDSP, nn SFLDSPCTL, nn SFLCLR, nn SFLEND(*MORE), where nn represents a conditioning indicator. You generally want to specify that the subfile-rows are 1 more than the rows-per-page. Also include any function key specs here.
You probably also want a record format for the bottom of the screen, to tell the user what function keys may be used. If so, use the OVERLAY keyword on the subfile-control record.
Processing
1.) Turn off SFLDSP and SFLDSPCTL to prevent them from showing on the screen for the moment. Turn on SFLCLR to enable creating an empty subfile. Now WRITE the subfile control record.
2.) Inside your loop processing rows from the database, increment the subfile record number, and WRITE a subfile record.
3.) When done filling in subfile records: Turn on SFLDSP and SFLDSPCTL so that both will be shown. Turn off SFLCLR so that your subfile records are not wiped out. Turn on SFLEND so that it will display "More..." at the bottom of each page, except the last. WRITE your footing record. EXFMT your subfile control record. Process any function keys as applicable.
