-----Original Message-----
From: Richard Hyatt [mailto:[EMAIL PROTECTED]]
Sent: Mittwoch, 2. Oktober 2002 16:40
To: Zabach, Elke; [EMAIL PROTECTED]
Subject: RE: SELECT ORDERED
> Elke,
>
> I'm not sure if I understand this:
>
> >Instead of this, one should check if the full join (without ROWNO) should be
>prepared
> >and then a select * from <join-resultset> where ... can be used.
>
> What would be the best way to mimic MySQL/Postgres's LIMIT keyword in SAPDB?
>
> I'm just trying to create a "page" view of rows within a single table, but I need to
>be able to move forward or backwards.
>
> -Richard
we had this in the archives:
LIMIT (x, y)
It is not possible, to use an offset directly.
Do :
SELECT * from <join-resultset> WHERE ROWNO <= 20
FETCH POS (10) INTO ... (if you want to see result number 10,
otherwise use 11)
Loop
FETCH NEXT INTO ...
Endloop
FETCH POS (n) jumps to the n-th result and returns it.
FETCH NEXT will give the next result from that point.
And if you like you can move forward and backwards
Elke
SAP Labs Berlin
Richard Hyatt
[EMAIL PROTECTED]
-----Original Message-----
From: Zabach, Elke [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 02, 2002 4:11 AM
To: Richard Hyatt; [EMAIL PROTECTED]
Subject: RE: SELECT ORDERED
Richard Hyatt wrote:
> I'm still quite new to SAPDB and was wondering if anyone had a simple example of the
>SELECT ORDERED statement. I'm trying to
> implement functionality similar to mySQL/PHP's LIMIT function. I found several
>references to ROWNO, but that will only work in one
> direction. I need to be able to move 1 page back (eg. 50 rows) from a current
>location. I know that it is possible to use cursors, but I thought
> that SELECT ORDERED may be an alternative.
select ordered will just return one row. You will have to specify 50 select
ordered-statements to
return those 50 rows (communication-time !!!) and you will have to assure that your
table-keys are specified correctly each time.
And select ordered will not be available with the next MAJOR release (7.4)
--> it is an idea, but it is not the best one.
I think, using ROWNO will be a better solution.
But please pay attention to the fact, that using ROWNO together with a join
will not minimize the work load to produce intermediate results (they will have
the same size with and without ROWNO), but only the final result will stop
after finding ROWNO results.
We had a customer using ROWNO with join, selecting 100 results, then
changing the where-clause a little bit to assure to receive the results 101-200,
then 201-300. And that is a very bad way for the kernel.
Instead of this, one should check if the full join (without ROWNO) should be prepared
and then a select * from <join-resultset> where ... can be used.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
