how to select highest value for a field with condition given?

Question

I'm doing time attendance system where my physical file(PF) have these fields:

User ID(key field),Date,Time In,Time Out
......

the problem is,I try to select for particular User ID and Date and highest value of Time Out for that day. and move the Time Out value to a variable in my program.

how to the RPGLE source would look like?

Don't separate date and time fields if they belong together. Unless you are _severely_ constrained for space, the amount saved isn't worth the headaches of dealing with it (mostly when using SQL - **WHICH YOU SHOULD BE**). — Clockwork-Muse, Jul 11 '12 at 16:06
Yes, you would be better advised to use embedded SQL, such as shown by @Buck below. It might take a bit more effort to understand the concepts, but it pays off handsomely, and can lead to designs which are simpler to write and run faster. — WarrenT, Jul 13 '12 at 22:56

score 3 · Accepted Answer · answered Jul 11 '12 at 12:03

3

The traditional way would be to build a logical file with User ID, Date and Time Out as keys, with Date and Time Out descending. Then I think just chaining to the logical using the User ID and today's date as a partial key will give you the latest record.

answered Jul 11 '12 at 12:03

Martin McCallion

743
1
4
22

thanks...another silly question to ask...can I declare the other field as key field?..even thought in my PF I only declare USRID as keyfield..??...should the keyfield on the same type(since I got error for the other field is not same type when I use Chain)?... – user1516536 Jul 16 '12 at 07:48
The key fields in the RPG program must match the key fields on the file. If you'd like an additional key field, create a logical file with the keys you need and use that in your RPG program. – Buck Calabro Jul 16 '12 at 15:56
@BuckCalabro, your statement as written is incorrect. For example, the file may be keyed by USER, Date, and Time In (or out)... and the program may reference only USER as a key. That is just fine. What I think you meant, is that your program cannot reference as a key, something that is not defined as a key in the file. The file will need to be recreated with the additional keys (NOTE! DATA LOSS!) or ALTER TABLE may be used to add constraints. Or, as you suggest, a new logical may be created. – Dennis Jul 30 '12 at 13:15
@Dennis, completely agree with you. I was trying to keep it simple. – Buck Calabro Jul 30 '12 at 14:00

score 2 · Answer 2 · edited Jul 11 '12 at 16:04

2

Embedded SQL will also work:

SELECT MAX(timeout) 
INTO :outTime  
FROM PF 
WHERE userid = :selected_user_id
AND date = :selected_date`

edited Jul 11 '12 at 16:04

Clockwork-Muse

12,806
6
31
45

answered Jul 11 '12 at 13:44

Buck Calabro

7,558
22
25

cannot use embedded SQL..any other suggestion – user1516536 Jul 16 '12 at 07:44
Ranked by personal preference from worst to less worse: read every record and keep track of the values yourself, FMTDTA, OPNQRYF are all alternatives to SQL. – Buck Calabro Jul 16 '12 at 16:05

score 2 · Answer 3 · answered Jul 14 '12 at 00:36

Let's say you've got an employee file, and an time worked file, such as you described. And you want to report what was the earliest time-in, and the latest time-out, for each employee on a given day.

So you might have a loop reading the employee file, and in that loop, you could chain out to the logical that @Martin described to get the last time-out, and another logical keyed with ascending time-in to get the least value there.

With SQL you could get the employee file information, as well as the earliest time-in and the latest time-out, all in a single read (fetch in SQL). It simplifies your code inside the loop quite a bit, doesn't it?

So here's the basic flow in pseudo-code:

Declare a cursor with your SQL statement
Open the cursor
Fetch a row from the cursor
DoWhile status is ok
    Process your data
    Fetch the next record
EndDo
Close the cursor

Not too scary looking, is it? So let's see what the code might look like. Of course there is more than one way to do in SQL, but let's start simple with just an SQL Select statement. When you use RPG variables in SQL, you prefix them with a colon (':').

    WITH h as
    ( SELECT empID, min(timein) as firstin, max(timeout) as lastout
        FROM workhours
        WHERE workdate = :myvariable
        GROUP BY empID
    )
    SELECT e.lastname, e.firstname, e.empnbr, h.firstin, h.lastout
      FROM h
      JOIN employees as e   on h.empID = e.empnbr
      ORDER BY e.lastname, e.firstname, e.empnbr;

Well, that's quite a SELECT statement, eh? A lot going on.

But it's organized into two parts. The first part is a table expression called h, where we get our times for each employee on that date. The second part lists all the result fields we want, which are taken from the table expression h and the employees file, wherever we can match the employee numbers in both files. The rows will be given to us sorted by last name, first name, and employee number.

So let's put this in RPG.

EXEC-SQL  DECLARE CURSOR C1 AS
              WITH h as
              ( SELECT empID, min(timein) as firstin, max(timeout) as lastout
                  FROM workhours
                  WHERE workdate = :myvariable
                  GROUP BY empID
              )
              SELECT e.lastname, e.firstname, e.empnbr, h.firstin, h.lastout
                FROM h
                JOIN employees as e   on h.empID = e.empnbr
                ORDER BY e.lastname, e.firstname, e.empnbr
                FOR READ ONLY;
EXEC-SQL  OPEN C1;
EXEC-SQL  FETCH FROM C1
            INTO :lname, :fname, :emp, :firsttime, :lasttime;
DoW &subst(SQLState,1,2) = '00';
    //
    // perform processing here
    //
    EXEC-SQL  FETCH FROM C1
                INTO :lname, :fname, :emp, :firsttime, :lasttime;
enddo;
EXEC-SQL  CLOSE C1;

Now you may at first hesitate at how much work is being done by the SELECT statement in the cursor. But by doing it all there you've simplified your I/O, and let the SQL optimizer work its magic to find the fastest way to get your data to you.

If you are unable to use embedded SQL, then Martin's answer might be a good choice. But if you would like to pursue the SQL option, then you could put the SELECT statement in a QM Query and send the output to a file. QM Query gives you the ability to specify variables, by prefixing the name with an ampersand as in CL. And the STRQMQRY command lets you pass in values for the variables. So you can say `STRQMQRY myqry OUTPUT(*FILE) OUTFILE(qtemp/results) SETVAR((&MYVARIABLE '''2012-07-14'''))`. — WarrenT, Jul 16 '12 at 14:21

how to select highest value for a field with condition given?

3 Answers3