RE: Update the first 1500 records that need updating

Hi Sue,

I tried what you suggested...

SELECT rrnb
FROM (SELECT RRN(b) AS rrnb
FROM filea b
WHERE FIELDA = 'X'
FETCH FIRST 1500 ROWS ONLY) A

And received the error
Keyword FETCH not expected. Valid tokens: ).

We are on V5R1. Do I have to be on a later version?

Rob

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Sue Romano
Sent: December 5, 2007 5:56 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Update the first 1500 records that need updating

Answers:

1) A derived table (also called a nested table expression) is a lot like a
CTE (common table expression) but you put it in the FROM clause. It can
only be referenced in the FROM clause where it is defined. A CTE can be
referenced anywhere in the query. You can't have a CTE in an UPDATE
statement, so you have to settle for using a derived table.

2) Yes, the X in the example is the name. The stuff you put in the FROM
clause needs to have a temporary table name that could be used. Even if
you don't ever use it.

3) Nested table expressions are discussed in the from-clause section of the
SQL Reference. Starts on page 417 of the V5R4 book. Page 389 of the V5R3
book.

Sue Romano
i5/OS SQL parser

------------------------------
date: Wed, 5 Dec 2007 13:52:29 -0600
from: "SJL" <sjl_abc@xxxxxxxxxxx>
subject: Re: Update the first 1500 records that need updating

Sue -
I was sooooo close...

Questions:

1) Is the 'derived' table analagous to a table created by a CTE? I tried
using a CTE to
no avail...

2) I suppose that X below is the name of the 'derived' table?

SELECT R FROM
(
SELECT RRN(F) R from FILEA F where FIELDA='X'
Fetch first 1500 rows only
) X

3) Where would you find documentation for this in the System i online
reference books?


- sjl