I've been browsing the ml about mysql-like LIMIT function to return for example only the 10 to 40 resultsets. I came to the conclusion that it was not possible 2 years ago unless by using cursors or the fetch function. So is there a brand new great function with 7.4 that can do that easily or will i have to test for hours and sending tons of mail to get my stuff work ?
Here is an abstract of what i got from the ml : > > Is there a limit or top function in SAP DB? > > > > MS SQL Server has a TOP function that is implemented like: > > SELECT TOP 25 column_1, column_2 FROM TableX WHERE ........ > > > > MySQL has a LIMIT function that is implemented like this: > > SELECT column_1, column_2 FROM TableX LIMIT 50, 25 > > > > The limit is nicer because it takes an offset so the mysql query above > would only return 25 > > results starting at 50. > > > > The top doesn't take an offset so it would return 25 results starting from > the top. > > A short part out of our reference manual: > > ROWNO predicate > > The ROWNO predicate restricts the number of lines in a result table (see > result table name). > > Syntax > > <rowno predicate> ::= ROWNO < <unsigned integer | parameter spec> > | ROWNO <= <unsigned integer | parameter spec> > > unsigned integer, parameter spec > > Explanation > > A ROWNO predicate may only be used in a WHERE clause that belongs to a QUERY > statement. The ROWNO predicate can be used like any other predicate in the > WHERE clause if the following restrictions are observed: > > The ROWNO predicate must be linked to the other predicates by a logic AND > The ROWNO predicate must not be negated > The ROWNO predicate may not be used more than once in the WHERE clause > You can specify the maximum number of lines in the result table using an > unsigned integer or a parameter specification. The specified value must > allow the result table to contain at least one row. If more lines are found, > they are simply ignored and do not lead to an error message. > > If a ROWNO predicate and an ORDER clause are specified, only the first n > result lines are searched and sorted. The result usually differs from that > which would have been obtained if a ROWNO predicate had not been used and if > the first n result rows had been considered. > > If a ROWNO predicate and a set function are specified, the set function is > only applied to the number of lines restricted by the ROWNO predicate. > > You see: an upper limit can be specified, but nothing like 'the 51th to 75 > row'. As mentioned before, SAP DB offers scrollable cursors. The SELECT / DECLARE CURSOR looks like usual, the FETCH according to this: <fetch statement> ::= FETCH [FIRST | LAST | NEXT | PREV] [<result table name>] INTO <parameter spec>,... FETCH [<position>] [<result table name>] INTO <parameter spec>,... FETCH [SAME] [<result table name>] INTO <parameter spec>,... <position> ::= POS (<unsigned integer>) | POS (<parameter spec>) | ABSOLUTE <integer> | ABSOLUTE <parameter spec> | RELATIVE <integer> | RELATIVE <parameter spec> If you say FETCH POS (50) you will get the 50th result row out of your result set (no matter if your result set was physically build and stored or not). And then you can do 24 time FETCH [NEXT] to catch 25 result rows starting at the 50th. // Thanks Elke If there is nothing new, i guess the fetch function would be the best. The problem with it is that i don't know if i can fetch a row and send it in the resultset i will return to my application... Usually i use fetch only to get a single value and to send it out with an out parameter. -- Marc-Olivier Meunier Ing�nieur Projet BKLINK MVH-IRIS 14, rue Rougemont 75009 Paris _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
