RE: SELECT ORDERED

[Richard Hyatt]

Title: Message

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

 

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