How to address dated records

[KennyAround]

version: 10.2B
platform: Linux

The scenario is that I need to retrieve the first most recently confirmed records with the earliest order date. I need 1 record only to be displayed as default on some screens.

define temp-table tTrack
 field account like customer.account
 field orderdate like order.orderdate
 field confirmdate like order.confirmdate.
.
.
account  orderdate   confirmdate
1231681  21/May/2012  25/May/2012
1392265  23/Mar/2012  25/Apr/2012
2409954  20/May/2012  25/May/2012
1684423  15/May/2012  25/May/2012
[COLOR=#ff0000][B]1759026  11/Apr/2012  25/May/2012[/B][/COLOR]<--
1849320  12/Mar/2012  25/Apr/2012
5906333  10/Jan/2012  25/Mar/2012
3260912  15/Mar/2012  25/Mar/2012
6263029  15/Feb/2012  25/Apr/2012

In the table above, the most recently confirmed record (25/May/2012) with the earliest order date is high-lighted. How to code to address that record?

Do I need to use "For Each...break by" and the First() function to get this record? or Is there any faster way to do so?

 

[KennyAround]

One of the possible ways is as follows,

define var vAcct like customer.account.
define var vOrderDate like order.orderdate.
define var vConfirmDate like order.confirmdate
.
define temp-table tTrack
 field account like customer.account
 field orderdate like order.orderdate
 field confirmdate like order.confirmdate
.
for each tTrack no-lock
 break by confirmdate DESCENDING by orderdate
 :
 if first(confirmdate) then
  assign
   vAcct = tTrack.account
   vOrderDate = tTrack.orderdate
   vConfirmDate = tTrack.confirmdate
. 
end.

But I wonder if there's any better way to do it. And if this is already the way, do I need to add indexes to the temp-table?

 

[mrobles]

define var vAcct like customer.account.
define var vOrderDate like order.orderdate.
define var vConfirmDate like order.confirmdate.
define temp-table tTrack
field account like customer.account
field orderdate like order.orderdate
field confirmdate like order.confirmdate.
FOR EACH tTrack NO-LOCK
BY confirmdate DESCENDING BY orderdate
ASSIGN vAcct = tTrack.account
vOrderDate = tTrack.orderdate
vConfirmDate = tTrack.confirmdate.
LEAVE.
END.

AND TRY USING FOR FIRST

MRobles

 

[TomBascom]

FOR FIRST is not reliable when you want the FIRST record in a particular sort order. Especially if the fields involved are not indexed.

One of the issues with the code as stated by the original poster is that there are no indexes in the temp-table. Maybe that is because it is sample code? If it is real code he would be well advised to add an index to support this query.

 

[KennyAround]

FOR FIRST is not reliable when you want the FIRST record in a particular sort order. Especially if the fields involved are not indexed.

One of the issues with the code as stated by the original poster is that there are no indexes in the temp-table. Maybe that is because it is sample code? If it is real code he would be well advised to add an index to support this query.

It is a new request for code changing. I think that I can actually add the index to the original Order table instead of creating a temp-table, as I notice that temp-table doesn't seem to support a composite index.

ADD INDEX "ConfirmOrderDate" ON "Orders"
AREA "Index1"
INDEX-FIELD "ConfirmDate" DESCENDING
INDEX-FIELD "OrderDate" ASCENDING

It seems in a temp-table, you can't specify a composite index sorted by ConfirmDate DESC first then OrderDate ASC by using "INDEX-FIELD". Is it true?

 

[TomBascom]

You should be able to do that in a temp-table index. The syntax would be:

define temp-table tTrack
  field account like customer.account
  field orderdate like order.orderdate
  field confirmdate like order.confirmdate
  index confirmOrderDate
    orderdate descending
    confirmdate
.

 

[KennyAround]

You should be able to do that in a temp-table index. The syntax would be:

define temp-table tTrack
  field account like customer.account
  field orderdate like order.orderdate
  field confirmdate like order.confirmdate
  index confirmOrderDate
    orderdate descending
    confirmdate
.

That's perfect. Thanks so much Tom.