How about this:
Keep a control record that has the last value so you know where it was last
time.
READ REC FROM F.CONTROL, "LAST.TEST" ELSE
REC = 0
REC<2> = 1000
END
LAST.TEST = REC<1> ;* last hit
INCREMENT = REC<2> ;* how much to jump w/ each test
TEST.ID = LAST.TEST
LAST.FOUND= LAST.TEST
DONE = @FALSE
LOOP
WHILE DONE = @FALSE
TEST.ID += INCREMENT
READV ZED FROM F.FILE, TEST.ID, 0 THEN
* id found, do another default increment
LAST.FOUND = NEXT.TEST
END ELSE
* id NOT found, so it must be between LAST.FOUND & NEXT.TEST
NEXT.TEST += INCREMENT ;* ensure someone does not exceed the
limit as you are testing
FOR XX1 = LAST.FOUND TO NEXT.TEST
READV ZED FROM F.FILE, XX1, 0 ELSE
HIGHEST.ID = XX1 -1
DONE = @TRUE
XX1 = NEXT.TEST ;* kill the loop
END
NEXT XX1
END
REPEAT
WRITEV HIGHEST.ID ON F.CONTROL, "LAST.TEST", 1
Obviously, you need to know your file and know how big it grows to determine
REC<2>.
John Israel
Senior Programmer/Analyst
Dayton Superior Corporation
1125 Byers Road
Miamisburg, OHÂ 45342
-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles Stevenson
Sent: Friday, May 27, 2011 9:33 AM
To: U2 Users List
Subject: Re: [U2] [UV] Emulate SQL MAX() function at TCL
I am going to assume we are talking about a HUGE file, otherwise
efficiency wouldn't much matter.
I don't know a GOOD way outside basic or sql to prevent a sort-selected
list from being created. For humongous files, that may be painful.
Here is a way, not a GOOD way, just a way. I just can't help myself:
1. Use an I-descriptor to compare last max to current id and set new max
if necessary.
2. Use REFORMAT to continually overwrite the same record with the newly
found max.
("737" is the biggest id in this example):
>CLEAR.FILE TINYWORK
File "TINYWORK" has been cleared.
>REFORMAT BIGDATA EVAL "'BIGDATA.MAXID'" EVAL "@2; IF @1 > @ID THEN
@1 ELSE @ID"
BIGDATA.MAXID
@2 ; IF @1 > @ID THEN @1 ELSE @ID
File Name = TINYWORK
>CT TINYWORK BIGDATA.MAXID
BIGDATA.MAXID
0001 737
>
>DISPLAY -- or if you prefer <<F(TINYWORK,BIGDATA.MAXID,1)>>
-- or if you prefer 737
>
QED
(That's Latin for "Quirky Example Demonstrated")
A related I-desc could be used as REFORMAT selection criteria to limit
the writes to only when the max changes. On average, that would halve
the writes.
A huge sort-select is going to be writing to uvtemp anyway, but I'd bet
your money it will be faster than the above.
__________
If you're going to do it inside basic, like some others have suggested,
you might as well try this to avoid creating a sorted select list in
memory / uvtemp:
SELECT FVAR
MAXID = ""
LOOP WHILE READNEXT ID
IF ID > MAXID THEN MAXID = ID
REPEAT
But test to see if it is a good idea.
I'm continually surprised by RetrieVe's efficiency compared to what I
can do in Basic.
On 5/27/2011 5:40 AM, Wols Lists wrote:
> On 27/05/11 07:03, Boydell, Stuart wrote:
>> Just wondering if anyone has a neat trick for picking up the highest ID from
>> a file. Where the file ID is an integer.
>>
>> With UVSQL I would SELECT MAX(@ID) FROM TABLE; - however, the program I need
>> to do this within has the file (table) open and if I try to execute it
>> returns the error 'Non-SQL re-entrant query calls are not allowed.'
>>
>> The best I have so far is:
>> st = 'SELECT FILE @ID BY.DSND @ID'
>> st<-1> = 'LIST ONLY FILE SAMPLE 1'
>> exec st capturing cap
>> maxId = trim(cap<4>)
>>
>> Any other ideas?
>>
> Extending your idea ...
>
> SELECT FILE @ID BY.DSND @ID SAMPLE 1
> READLIST
>
> Not quite sure whether SAMPLE is the correct keyword, because I think
> one keyword samples then sorts (that might be SAMPLING), but I'm sure
> there is one that sorts then samples.
>
> If you can't find the keyword, the other trick could be
>
> SELECT FILE @ID BY.DSND @ID
> READNEXT
> CLEARLIST
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
