RE: SQL documentation

I think correct manual is SQL Reference.
The Subselect is in this case used as nested table expression. Search on
from-clause or nested-table-expression at this link:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstsubs
elect.htm#subselect

While ROW_NUMBER() built-in is new with V5R4, neither Subselects nor nested
table expressions are new.
Perhaps a more recognizable form of this statement would use a CTE (Common
Table Expression), i.e.:

WITH PRODUCT_TEMP AS
(
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_PRICE,
rownumber() OVER (ORDER BY PRODUCT_ID) AS ROW_NEXT
FROM PRODUCT,PRODUCT_CATEGORY
WHERE PRODUCT. PROD_CATEGORY_ID = PRODUCT_CATEGORY.CATEGORY_ID AND
PRODUCT_CATEGORY.CATEGORY_ID = 'Books' AND
PRODUCT. PRODUCT_DESCRIPTION LIKE 'Application Servers'
)
SELECT *
FROM PRODUCT_TEMP
WHERE ROW_NEXT BETWEEN ? and ?

BTW, CTEs are not new either (been around since V4R4).

All that said, sounds to me like you're looking for a regular SQL
programming book. I think any book geared toward ANSI SQL rather than being
db vendor or platform specific would suffice.

Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL documentation

I'm trying a technique I found on the web, and I'd like to see the
manual on it but I'll be darned if I can figure out which one has it.
First, the technique from
http://www.ibm.com/developerworks/db2/library/techarticle/0307balani/0307bal
ani.html

SELECT * FROM (
SELECT PRODUCT_ID, PRODUCT_NAME,
PRODUCT_DESCRIPTION, PRODUCT_PRICE,
rownumber() OVER
(ORDER BY PRODUCT_ID) AS ROW_NEXT
FROM PRODUCT,PRODUCT_CATEGORY
WHERE
PRODUCT. PROD_CATEGORY_ID
= PRODUCT_CATEGORY.CATEGORY_ID
AND
PRODUCT_CATEGORY.CATEGORY_ID = 'Books'
AND
PRODUCT. PRODUCT_DESCRIPTION LIKE
'Application Servers'
)
AS PRODUCT_TEMP WHERE
ROW_NEXT BETWEEN ? and ?

This works in DB2 UDB for i5/OS V5R4. What I'm trying to work out is
where I can find the documentation for this, specifically the SELECT ...
AS PRODUCT_TEMP.

I looked in the SQL Reference and the Programming guide. I'm guessing
it's related to the OLAP [ROW_NUMBER or rowid()] which I see in the
Reference examples, but not in the syntax diagrams.
--buck