Re: Limiting rows in select statement....

It turns out that for V7R1 OFFSET and LIMIT are not available without PTFs/TR.

So I ended using ROW_NUMBER() OVER() to solve the problem.

Thanks for the suggestions.  We're supposed to be moving to V7R3 in March so I look forward to using both RPG and SQL new features.

Rob


On 1/5/2018 2:17 PM, Roger Harman wrote:

Another option is to use RRN(). Assuming, you have no rrn gaps.

select * from YourTable
where rrn(YourTable) between 21 and 40

I *think* OFFSET (& LIMIT) are predicated on a certain DB2 level on V7.2 or V7.3.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power



From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Robert Rogerson <rogersonra@xxxxxxxxx>
Sent: Friday, January 5, 2018 9:34 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Limiting rows in select statement....

Thanks Charles, I'll read up on them.

Rob


On 1/5/2018 12:21 PM, Charles Wilt wrote:

See the new OFFSET (& LIMIT) clauses...

--rows 21 - 40
Select * from totalfile
offset 20 rows
fetch first 20 rows only





On Fri, Jan 5, 2018 at 10:10 AM, Robert Rogerson <rogersonra@xxxxxxxxx>
wrote:

Hi All,

I think there's an easy way to do this but I can't think of it at the
moment.

I have totalfile with 60 records.

I want to insert the first 20 rows into filea.  Rows 21 thru 40 into
fileb.  Rows 41 thru 60 into filec.

File a is easy...

INSERT INTO filea

SELECT * FROM totalfile FETCH FIRST 20 ROWS ONLY

My question is how do I specify the insert statements for fileb and filec?

Thanks,

Rob

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l

RPG400-L Info Page - midrange.com
lists.midrange.com
To unsubscribe from RPG400-L, get a password reminder, or change your subscription options enter your subscription email address:

or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

RPG400-L mailing list archive (midrange.com)
archive.midrange.com
midrange.com RPG400-L mailing list archive

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD