title: In DB2 SQL, how can I return a variable number of rows with FETCH FIRST (n) ROWS ONLY..?
body: <p>How can I return a variable (n) number of rows with <code>FETCH FIRST (n) ROWS ONLY</code> in DB2 SQL..? This is for DB2/400 v7r3.</p>
  
  <p>The <a href="https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzfetchfirst.htm" rel="nofollow noreferrer">documentation for <code>FETCH</code></a> does clearly state it cannot be done, but I cannot imagine another way to do it...</p>
  
  <blockquote>
    <p>...fetch-row-count must not contain a scalar-fullselect, a column reference, a table reference, a user-defined function reference, or a built-in scalar function...</p>
  </blockquote>
  
  <p>...which I take as meaning it must be a constant such as <code>"10"</code>, and cannot be a variable or column name, which unfortunately is exactly what I would like to do.</p>
  
  <p>This works:</p>
  
  <pre><code>SELECT      PREFILTER.*
  FROM        PREFILTER
  INNER JOIN  GT1 ON FILTERED.GTAMT=GT1.LOSTAMT
  ORDER BY    GTDATE DESC
  FETCH FIRST 10 ROWS ONLY
  </code></pre>
  
  <p>This does not work:</p>
  
  <pre><code>SELECT      PREFILTER.*
  FROM        PREFILTER
  INNER JOIN  GT1 ON PREFILTER.GTAMT=GT1.LOSTAMT
  ORDER BY    GTDATE DESC
  FETCH FIRST (GT1.LOSTAMT) ROWS ONLY    &lt;=== changed here
  </code></pre>
  
  <p>The following error is received:</p>
  
  <pre><code>SQL Error [428H7]: [SQ20467] Expression containing LOSTAMT must calculate a constant value.
  </code></pre>
  
  <p>This query is intended to find financial transaction records which are orphaned or keyed incorrectly in a larger table with rather loose normalization &amp; referential integrity (or lack thereof). </p>
  
  <p><code>PREFILTER</code> is a query which returns a subset of the main table, and <code>GT1</code> is another query which calculates a smaller &amp; more complex subset of those records. Then <code>JOIN</code> them, and return <code>(n)</code> rows as specified by the column <code>LOSTQTY</code>, in descending date order. So it should only return the (n) most recent records.</p>
  
  <p>Note that I do recognize my placement of <code>FETCH</code> is incorrect, and it (or whatever it morphs into) would likely have to move into one of the CTE queries such as <code>GT1</code>.</p>
  
  <p>Also, at this early stage, it seems apparent I'm getting some Cartesian results, but that will likely be settled once the <code>FETCH</code> issue is resolved.</p>
  
  <p>For reference, here is the full SQL of the project:</p>
  
  <pre><code>WITH  --SET THE INITIAL ACCOUNT &amp; DATE RANGE  
          PREFILTER    AS  (   SELECT      *
                              FROM        GLTRANT
                              WHERE       GTDATE &gt; 20170000
                              AND         GTACCT=112068
                          ),
        --CREATE LIST OF ALL POSITIVE VALUES
          POSVALS     AS  (   SELECT      GTAMT      AS POSAMT, COUNT(GTAMT) AS POSC
                              FROM        PREFILTER 
                              WHERE       GTAMT &gt; 0
                              GROUP BY    GTAMT
                          ),
        --CREATE LIST OF ALL NEGATIVE VALUES, WITH SIGN DROPPED
          NEGVALS     AS  (   SELECT      ABS(GTAMT) AS NEGAMT, COUNT(GTAMT) AS NEGC
                              FROM        PREFILTER 
                              WHERE       GTAMT &lt; 0
                              GROUP BY    ABS(GTAMT)
                          ),
        --CALCULATE DISCREPANCIES BETWEEN THE TWO LISTS. SUBTRACT THE TWO AND MULTIPLY THE SIGN BY THE ABSOLUTE VALUE 
        --OF THE DIFFERENCE. THEN TO RESTORE THE SIGN, MULTIPLY THE AMOUNT BY THE SIGN OF THE DIFFERENCE. 
          FOJ         AS  (   SELECT      SIGN(COALESCE(POSC,0)-COALESCE(NEGC,0))*COALESCE(POSAMT,NEGAMT) AS LOSTAMT, 
                                          ABS (COALESCE(POSC,0)-COALESCE(NEGC,0))                         AS LOSTQTY
                              FROM        POSVALS 
                              FULL OUTER JOIN NEGVALS ON POSAMT=NEGAMT
                              WHERE       COALESCE(POSC,0)-COALESCE(NEGC,0) &lt;&gt; 0
                          ),
        --GET DISCREPANCIES WITH COUNT &gt;1 
          GT1         AS  (   SELECT      *
                              FROM        FOJ
                              WHERE       LOSTQTY&gt;1 
                          )
  
  --SEARCH PREFILTER FOR EACH AMOUNT (LOSTAMT) IN GT1 AND RETURN THE MOST RECENT (LOSTQTY) RECORDS 
  SELECT      PREFILTER.*
  FROM        PREFILTER
  INNER JOIN  GT1 ON PREFILTER.GTAMT=GT1.LOSTAMT
  ORDER BY    GTDATE DESC
  FETCH FIRST (GT1.LOSTQTY) ROWS ONLY --DOES NOT WORK
  </code></pre>