Re: SQL - ROWNUM -- MIDRANGE-L

On 15-May-2015 10:31 -0500, Mohan Eashver wrote:


I am writing a SQL script to be run interactively on STRSQL session.
The file has several thousand rows.
I like to retrieve let say only the first 100 rows.
What is the Syntax to use ?

I know that I was able to something like this in ORACLE world.
But ROWNUM is not recognized on IBM i (we are on V7R1)

SELECT * FROM ORDERS WHERE ROWNUM < 101

The Start Interactive SQL (STRSQL) by default, running output todisplay, will operate implicitly much like having specified the clauseOPTIMIZE FOR 25 ROWS, so the effect may be similar, even without anexplicit limitation for the number of row; i.e. generally [notably, if"ORDERS" is a TABLE] the control is returned to the report writer afterthe query obtains that many rows, and only enough rows are obtained byFETCH to present at the display, so the user sees only as many as theywish to /scroll/ through -- but of course that could be beyond 100.

However the ROWNUM concept generally implies an ORDER BY, of whichthe OP did not specify; without an ORDER BY, which of the rows are the/first 100/ is undefined and thus unpredictable.

If collation is irrelevant then just add the FETCH FIRST 100 ROWSONLY clause to the end of that SELECT statement, and remove the WHEREclause:


    SELECT *
    FROM ORDERS
    FETCH FIRST 100 ROWS ONLY

More likely the desired effect is to obtain the First 100 rowsaccording to a particular collation. For that, use the OLAPspecifications<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafyolap.htm>,but AFaIK that necessarily will make the desired query much morecomplicated, unless the inclusion of the value for the ROWNUM in theresult set is acceptable, despite that is not what the ORACLE queryeffects; e.g. one of the following:


    -- again using FETCH FIRST instead of less-than predicate
    -- notice the ROWNUM column is now part of the result set
    SELECT
      ROW_NUMBER() OVER(ORDER BY Some_Key) as ROWNUM
    , T1.*
    FROM ORDERS as T1
    FETCH FIRST 100 ROWS ONLY

Note: Remove the "ORDER BY Some_Key" and order is unspecified; thatof course accomplishes nothing different than the earlier query, exceptadding the [possibly unwanted] ROWNUM values to the result set.


    -- using less-than predicate instead of FETCH FIRST; without
    -- the FETCH FIRST, the query likely performs much more work
    -- best IMO to keep the FETCH FIRST in the subquery and thus
    -- omit the then-redundant predicate in the WHERE clause
    SELECT N1.* /* or all columns named, except N1.ROWNUM */
    FROM ( SELECT
             ROW_NUMBER() OVER(ORDER BY Some_Key) as ROWNUM
           , T1.*
           FROM ORDERS as T1
           /* FETCH FIRST 100 ROWS ONLY */
         ) as N1
    WHERE ROWNUM < 101

Note: Obviously the column-list for the above query encapsulated in aVIEW is often ideal as contrasted with composing a complete column-listin ad-hoc queries.


    -- using less-than predicate instead of FETCH FIRST
    -- and join on primary key to omit the ROWNUM values
    -- best IMO to keep the FETCH FIRST in the
    -- derived\Nested Table Expression (NTE) and again
    -- omit the then-redundant predicate in the WHERE clause
    SELECT F1.*
    FROM ORDERS as F1
    JOIN ( SELECT
             ROW_NUMBER() OVER(ORDER BY Some_Key) as ROWNUM
           , T1.Pri_Key
           FROM ORDERS as T1
           /* FETCH FIRST 100 ROWS ONLY */
         ) as N1
      ON F1.Pri_Key = N1.Pri_Key
    WHERE N1.ROWNUM < 101

    -- AFaIK there is no support for the following syntax which
    -- would eliminate the ROWNUM value from the result-set
    SELECT *
    FROM "ORDERS"
    WHERE ROW_NUMBER() OVER(ORDER BY Some_Column ASC) < 101
    FETCH FIRST 100 ROWS ONLY

After I finished composing my reply, I figured this must be a FAQ, soI did a simple web search:

<https://www.google.com/search?q=db2+equivalent+of+rownum+of+oracle>

  Among other links, that led me to a link that directed me to:
<http://www.ibm.com/developerworks/data/library/techarticle/dm-0707rielau/>
DB2 Viper 2 compatibility features
Make your application ports easier
"...
_Limit result sets_

Sometimes it is necessary to limit the number of rows returned by aquery. Oracle applications typically utilize the ROWNUM pseudo columnfor that purpose. In DB2, there are two options to achieve the same effect:


    FETCH FIRST n ROWS clause
    ROW_NUMBER() OLAP function
..."

But what the article covers is, of course, not about the DB2 for i.But the snippet helps explain what the ORACLE ROWNUM is [apseudo-column] and what standard DB2 offers in-kind to simulate; thecapability of the DB2 variant on IBM i getting such a "compatibilityfeature" [partly as an extension with allowing the ROW_NUMBER() in aWHERE clause] as something possible in the future -- as something whichI am confident I have not seen noted yet anywhere as available, not inany docs or developerWorks discussions that I have read.