Re: [U2] [UV] Emulate SQL MAX() function at TCL
Sorry for the late response, but what about the max keyword i.e. LIST file MAX @ID? It returns the maximum value equivalent to the first value listed in a sort ascending i.e. the justification of the field will affect the order. Regards Kurt Neumann; U2 Certified Solutions Expert -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Boydell, Stuart Sent: 27 May 2011 08:04 AM 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(cap4) Any other ideas? Cheers, Stuart ___ 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
Re: [U2] [UV] Emulate SQL MAX() function at TCL
Thanks Kurt - I didn't know about that one. I'll store the idea for another time. Cheers, Stuart -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kurt Neumann Sent: Thursday, 2 June 2011 19:01 To: U2 Users List Subject: Re: [U2] [UV] Emulate SQL MAX() function at TCL Sorry for the late response, but what about the max keyword i.e. LIST file MAX @ID? It returns the maximum value equivalent to the first value listed in a sort ascending i.e. the justification of the field will affect the order. Regards Kurt Neumann; U2 Certified Solutions Expert ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
Create an index on the @ID makes it easy/faster to find :-) Ross Ferris Stamina Software Visage Better by Design! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles Stevenson Sent: Friday, 27 May 2011 11:33 PM 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(cap4) 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
[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(cap4) Any other ideas? Cheers, Stuart ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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(cap4) Any other ideas? Cheers, Stuart ___ 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
Re: [U2] [UV] Emulate SQL MAX() function at TCL
Hi Stuart, Probably too late now... but... If you defined your SQL table with the DEFAULT NEXT AVAILABLE it will automatically increment the primary key value by 1 when you insert a new row... CREATE TABLE MYTABLE (CLIENT_NUMBER INT PRIMARY DEFAULT 'NEXT AVAILABLE', CLIENT_NAME VARCHAR FMT 30T DEFAULT '', CREATE_ADDRESS VARCHAR FMT 30T DEFAULT '' ... See UniVerse SQL Reference Guide sqlref.pdf page 113 (UV11.1 docset). Regards, David -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Boydell, Stuart Sent: Friday, 27 May 2011 4:04 PM 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(cap4) Any other ideas? Cheers, Stuart ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ** IMPORTANT MESSAGE * This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. ** ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
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(cap4) 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 Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
You could try: SELECT FILE READLIST IDS ELSE ABORT MAX.ID = MAXIMUM(IDS) rex On Fri, May 27, 2011 at 6:40 AM, Wols Lists antli...@youngman.org.uk 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.' ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
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(cap4) 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
Re: [U2] [UV] Emulate SQL MAX() function at TCL
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 REC2 = 1000 END LAST.TEST = REC1 ;* last hit INCREMENT = REC2 ;* 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 REC2. 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(cap4) 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
Re: [U2] [UV] Emulate SQL MAX() function at TCL
What comes to mind are these: 1. Have a the system write the Max key somewhere. 2. Create a Trigger that keeps a max key updated. 3. Create an Index then use a SUBR to access the Max Key. 4. If this statement is used a lot then every time you run it update your own Max Key and start looking from that point. David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Boydell, Stuart Sent: Thursday, May 26, 2011 11:04 PM 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(cap4) Any other ideas? Cheers, Stuart ___ 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
Re: [U2] [UV] Emulate SQL MAX() function at TCL
On 27/05/11 14:33, Charles Stevenson wrote: 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. Hmmm... Assuming a huge file, as you suggest, create an index on @ID! Indexes are always sorted, aren't they? So either make it sort downwards or, if it's numeric, index on nought minus id :-) Then you can just SELECTINDEX READNEXT CLEARLIST All done! Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
That's the best answer yet, Wol. Assuming there's no Next-Sequential-Id counter to poll. assuming a large file, and an ongoing need. (If not, who cares?) assuming normal writes to the file aren't so frequent that maintaining the extra index -- albeit ever so evenly distributed -- isn't too taxing compared to the occasionally need for that max. Probably reasonable assumptions, all. cds On 5/27/2011 10:34 AM, Wols Lists wrote: On 27/05/11 14:33, Charles Stevenson wrote: 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. Hmmm... Assuming a huge file, as you suggest, create an index on @ID! Indexes are always sorted, aren't they? So either make it sort downwards or, if it's numeric, index on nought minus id :-) Then you can just SELECTINDEX READNEXT CLEARLIST All done! Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] [UV] Emulate SQL MAX() function at TCL
:) Thanks all -Original Message- From: Wols Lists Sent: Saturday, 28 May 2011 1:34 To: u2-users@listserver.u2ug.org Subject: Re: [U2] [UV] Emulate SQL MAX() function at TCL On 27/05/11 14:33, Charles Stevenson wrote: 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. Hmmm... Assuming a huge file, as you suggest, create an index on @ID! Indexes are always sorted, aren't they? So either make it sort downwards or, if it's numeric, index on nought minus id :-) Then you can just SELECTINDEX READNEXT CLEARLIST All done! Cheers, Wol ___ 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
