retrieving the last record from the field.

[smart.Object]

Hello me again.

Need to retrieve the last record and then display it. It shouldn't lock anything either...

Thanks for input.

 

[parul]

"Last record" is found by the index that is used in the query.
so lets say
find last <table name> no-lock.
will give you last record of the table by using the primary index.

so, be careful.

-Parul.

 

[smart.Object]

Thanks parul. Is it possible to retrieve last entry in a column thou? For example from field like customer.address.
thanks again.

 

[TomBascom]

You need to think about this.

"Last" is relative to some sort order. If the column in question is sufficiently indexed then FIND LAST can specify the index and obtain the wanted record. If the column is not then you will need some other method such as:

/* untested */
for each customer no-lock by customer.address:
end.
display customer.

But beware such constructs perform very poorly. If there is much data in the table they will take a very, very long time to run.

Personally, I am deeply suspicious of the need to find the LAST (or the FIRST) of anything. It is almost always the result of a misunderstanding of some requirement or other.

 

[smart.Object]

Thanks for reply Tom. actually the column I have is called pack-no, and numbers are assigned in order. So the hard way could be sort the column and give me the larges number... But how would I go about coding that? .

Also would it be hard to index the whole column and reference it in my code somehow?
Thanks

 

[tamhas]

If you need to search on a column, then that is a pretty strong indication that you might want to index it, especially if the table has a lot of records.

Note that you can do a FOR EACH, specify the sort order using BY and then leave the loop after the first record to get FIRST or specify DESCENDING to get last. But, that is only efficient if there is an index to support the BY since otherwise it needs to read all records.

 

[smart.Object]

here is what I got, but it doesn't like the last statement

FOR EACH pack-line WHERE pack-line.company = g-co-id
AND pack-line.order-no = static-so-no
NO-LOCK use-index order-line by pack-line.pack-no:
END.
last-pack = pack-line.pack-no.

Last pack is the end variable I guess...

 

[TomBascom]

The original code that I posted doesn't quite work...

for each customer no-lock break by address:
  if last( address ) then leave.
end.
display customer.

This is still a bad idea.

From your somewhat improved description of the requirements I still cannot determine why you want to do this. I have a suspicion that you're doing it in order to increment the pack-no.

If that is the case then you should probably be using a sequence instead. "FIND LAST and increment" is a (very) old-school way of doing things and it suffers from a number of downsides. One of which is record locking contention under load. This sort of code "works" in a single user development environment but it can cause havoc when released into a multi-user production environment. Especially a large scale one.

If there is some other reason why you need to find the last pack-no then you should at least add an index on pack-no. (Although I still expect that a careful examination of the requirement could eliminate the need.)

 

[tamhas]

OK, so it isn't that you need to find the last pack-no overall, but the last one within a company and order. Right? As Tom says, it would be interesting to here the business reason for this, but the selection within the set makes the problem a little different than it originally seemed.

What is in that index?

What you really want here is to be able to read only one record, otherwise you are likely to have poor performance. So, what indexes already exist?

 

[smart.Object]

inside the pack-line index there are 3 fields company, pack-no, and pack-line. What I need to do is when the company that purchased from us receives our product and somehow misplaces the packing number they can call us, we pull up our shipping program, input the order number and operator can easily see the last packing number from the order, its just a matter of convenience.

I think I am not getting the concept of indexing as well. What exactly does indexing a particular column accomplishes? Also here is the code from the old program that used to work...:

for each pack-line
where pack-line.company = g-co-id
and pack-line.order-no = static-so-no
no-lock use-index order-line :
if not last-pack matches ("*" + string(pack-line.pack-no) + "*")
then last-pack = last-pack + string(pack-line.pack-no) + ",".
end. /*for eachpackline gb 7/21*/

Also tamhas we only have one company, but for some reason I have to include the g-co-id statement, otherwise processes take forever (i guess b/c it was designed for multi company).
Thanks.

 

[tamhas]

It would help a lot to know what indexes currently exist.

You need to include company because that is the top level field in the relevant index. Without it, you are reading every record in the table.

Indexes help you "bracket" to a minimal number of records. E.g., if you have an index on company and order number and you specify a company and an order number, then Progress will read *only* the records which match those values, presumably a much smaller set than the whole table.

If you give us all of the existing indexes, we can tell you if there is an easy way to get this without a new index.

How many records are there likely to be which match on company and order?

Have you tried
BY Company BY Order BY PackNumber DESCENDING with no index specified?

Do this is a small test program and compile with the XREF option to see what indexes it will pick and show a list of records to show you what it will return and in what order. If you get the right set in the right order, you can then leave the loop after the first record and there you are.

 

[smart.Object]

There are a total of 4 indexes connected to pack-line:
inv-line (fields: company, inv-no, inv-line)
item-no (company, item-no)
order-line (company, order-no, order-line)
pack-line (company, pack-no, pack-line)

there are probably up to 10 records that will match to order number and packing number...

I tried to do the " BY Company BY Order BY PackNumber DESCENDING" but it can't even find the pack-line I was trying to figure out how to compile with XREF but with no luck... Progress help is not very helpful, would I need to declare XREF like so XREF { xreffile | VALUE ( expression ) } [ APPEND [ logical-expression ] ] and how would it apply to my situation...
Thanks tamhas.

 

[tamhas]

You did use "BY company BY order-no BY pack-no", not my pseudo code?

COMPILE progname XREF progname.xrf

or whatever destination name you want.

I would try

for each pack-line no-lock
where pack-line.company eq "yourcompany"
and pack-line.order-no eq someorder
by pack-line.company
by pack-line.order-no
by pack-line.pack-no descending
:
display pack-line.pack-no.
end.

If that gets you the sequence you want, then change the display to set a work variable and leave the loop after the first one.