I need help with a query statement. I'm wanting to list the last event for each field that has had an event after 6/1/09.
vQuery = "FOR EACH field NO-LOCK, LAST event NO-LOCK WHERE event.num-field = field.num-field AND event.dt-end >= 6/1/2009 BY event.dt-end".
This query is listing all of the events after 6/1/09, not just the last event record.
Correction:
Actually, it is finding the last record, but in my Repeat loop for every record > 6/1/09 it is putting a duplicate copy of that last event record into the temp table that I am creating.
Maybe we need to see more of the code?
Have you tried just listing out the result?
I'm not sure you can do this in a single statement. Have you considered a FOR EACH with BREAK-BY and picking up the LAST-OF record?
When I run the code in a procedure window and display, it appears to run fine:
FOR EACH Field NO-LOCK WHERE Field.num-field = 49,
LAST Events NO-LOCK WHERE Events.num-field = Field.num-field AND Events.dt-end >= 06/01/2009 BY Events.dt-end:
DISPLAY Field.num-field
Events.dt-end.
END.
Result is just one record 49 8/31/2009
Here is how it is actually run:
vQuery = "FOR EACH Field NO-LOCK WHERE Field.num-field = 49,
LAST Events NO-LOCK WHERE Events.num-field = Field.num-field AND Events.dt-end >= 06/01/2009 BY Events.dt-end:".
hdlSchedulesQuery:QUERY-PREPARE(vQuery).
hdlSchedulesQuery:QUERY-OPEN.
/*** Read the query ***/
blkReadSchedules:
REPEAT:
hdlSchedulesQuery:GET-NEXT().
IF hdlSchedulesQuery:QUERY-OFF-END THEN DO:
LEAVE blkReadSchedules.
END.
CREATE tmpSchedulesView.
ASSIGN tmpSchedulesView.num-field = Field.num-field
tmpSchedulesView.dt-end = Events.dt-end.
END. /* blkReadSchedules */
Then when I display tmpSchedulesView, I get six records:
49 8/31/2009
49 8/31/2009
49 8/31/2009
49 8/31/2009
49 8/31/2009
49 8/31/2009
(when there are actually six records with different dates > 6/1/09, I only want it to display the last record).
LAST is on my personal keyword forget list. One reason is that it does not do what you expect it to do in a FOR loop. There is some convoluted explanation offered as to why what it really does do is the right thing for it to do but I couldn't repeat it without looking it up...
Thanks Tom.
When I changed the query to ...FIRST Events...
BY Events.dt-end DESCENDING
then it seemed to work fine.
I should mention that FIRST is on my KWFL for the same reason... if it is working it is probably not because it is really doing what you want for the reasons that you think that it is doing them.