Stuart You're stuck with something like that, unless you know that all the possible ids are present in the file. If so, you could use a form of binary search pattern could find it quickly.
i.e. start with a likely number and see if the id is there. (filesize / recordsize) If it is, double it. If it is not, reduce it by half the difference to the previous number and try that. If that is found, increase by half the difference. If not, reduce by half the difference again. And so on. If you follow the maths, it can scan wide ranges very quickly indeed.. just like a binary sort algorithm. And may be quicker than calling out to sort the entire file. But it only works if there are no gaps in the numbering. Brian -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Boydell, Stuart Sent: 27 May 2011 07:03 To: U2 Users List Subject: [U2] [UV] Emulate SQL MAX() function at TCL 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? Cheers, Stuart _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
