Marc-Olivier Meunier wrote: > > > 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
NO > or will i have to test for hours and sending tons of mail to get > my stuff work ? I hope, not Elke SAP Labs Berlin > > 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 > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
