RE: SQL and retrieving the next xx rows -- MIDRANGE-L

Brad,

This works for me at v5r2:

SELECT 'A',locnbr, rand() FROM tsrcdtp WHERE locnbr = '00B61'
union all
SELECT 'B',locnbr, rand() FROM tsrcdtp WHERE locnbr = '00B61'
order by 3
fetch first 20 rows only

I get only 20 records total, some from each file. Though it's not particularly quick since the UNION
ALL and ORDER BY mean the entire results set needs to be built.

Question, are you trying your statement as a cursor in an RPG program, or in STRSQL?

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
Sent: Tuesday, July 08, 2008 2:28 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL and retrieving the next xx rows

Yes, the problem I seem to be having is a UNION ALL. I added a FETCH FIRST
clause and I'm still getting all bazillion records into the result set.

I want to tell it "after 100 rows, just stop looking". But I suppose it's
not that easy using a union.

Here's my SQL now:



Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Tuesday, July 08, 2008 1:09 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL and retrieving the next xx rows

Bradley,

Take a look at the FETCH FIRST clause of the select statement. I think
that might be what you are looking for. IIRC there is also a FETCH NEXT
but it requires a cursor.

select.... where ordernum >= searchorder fetch first 100 rows only

HTH,

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
Sent: Tuesday, July 08, 2008 11:00 AM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: SQL and retrieving the next xx rows

When running an SQL statement on say, and order file, lets say you were
letting the user enter an order number to search by used in the SQL

ie...

Select..... where ordernum = searchorder

Now, lets say that you wanted to also select the next 100 orders after
the order number entered. The problem being that the order numbers are
not sequential.

So we could have:

101
110
111
120
121
122

etc...

So we'd want to say:

select.... where ordernum >= searchorder

but only have it select the next 100 orders (mainly because if we
didn't, the search would take forever if there were a bazillion orders
in the database).

I have seen reference to a LIMIT keyword, but it didn't seem to like it
in the SQL interface.

Thanks!

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

Privileged and Confidential. This e-mail, and any attachments
there to, is intended only for use by the addressee(s) named
herein and may contain privileged or confidential information.
If you have received this e-mail in error, please notify me
immediately by a return e-mail and delete this e-mail. You are
hereby notified that any dissemination, distribution or copying
of this e-mail and/or any attachments thereto, is strictly prohibited.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.