Row Count in UNION for DB2 1

[newdbprgr]

Faced with this problem, any idea how to go about it?

SELECT * FROM (
(SELECT COL1, COL2, MAX(COL3),
ROWNUMBER() OVER (ORDER BY COL1) AS ROW_NEXT
WHERE COL1 = 'A'
GROUP BY COL1, COL2)
UNION
(SELECT COL1, COL2, COL3,
ROWNUMBER() OVER (ORDER BY COL1) AS ROW_NEXT
WHERE COL1 != 'A'
GROUP BY COL1, COL2, COL3)
) AS TEMP_DATA
WHERE ROW_NEXT BETWEEN upper AND lower; //upper and lower are passed in value for pagination

It returns a seperate set of row number. For example, in set 1 - 1,2,3,4,5,6 and set 2 - 1,2,3

SELECT *,ROWNUMBER() OVER (ORDER BY COL1) AS ROW_NEXT
FROM (
(SELECT COL1, COL2, MAX(COL3)
WHERE COL1 = 'A'
GROUP BY COL1, COL2)
UNION
(SELECT COL1, COL2, COL3
WHERE COL1 != 'A'
GROUP BY COL1, COL2, COL3)
) AS TEMP_DATA
WHERE ROW_NEXT BETWEEN upper AND lower; //upper and lower are passed in value for pagination

Then i tried to take the rownumber out, but 'WHERE ROW_NEXT BETWEEN upper AND lower' condition is not recognised. Any idea to get a continous row count

 

[blom0344]

I think that referencing the rownumber() function in the where clause of the outer select is not allowed.
You need an additional nested level..

Ties Blom

 

[newdbprgr]

just as i thought... you've helped to confirm my thought. thanks.