Perl prepare DB2 statement not returning what I need

Question

Hey,

Since I am using DB2, in order to select a portion of a database in the middle (like a limit/offset pairing), I need to do a different kind of prepare statement. The example I was given was this:

SELECT * FROM (SELECT col1, col2, col3, ROW_NUMBER() OVER () AS RN FROM table) AS cols WHERE RN BETWEEN 1 AND 10000;

Which I adapted to this:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) AS foo WHERE rownum >= 500 AND rownum <1000

And when I call the fetchall_arrayref(), I do come out with 500 results like I want to, but it is only returning an array with references to the row number, and not all of the data I want to pull. I know for a fact that that is what the code is SUPPOSED to do as its written, and I have tried a bunch of permutations to get my desired result with no luck.

All I want is to grab all of the columns like my previous prepare statement into an array of arrays: SELECT * FROM TU_TRANSACTIONS ORDER BY 2, 3, 4, 6, 7

but just on a designated section. There is just a fundamental thing I am missing, and I just cant see it.

Any help is appreciated, even if its paired with some constructive criticism.

Answer 1

I don't use DB2 so I could be off-base but it seems that:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) AS foo WHERE rownum >= 500 AND rownum <1000

Would only return the row numbers because while the sub-query references the table the main query does not. All it seems it would see is the set of numbers (which would return a single column with the number filled in)

Perhaps this would work:

SELECT * FROM TRANSACTIONS, (SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) AS foo WHERE rownum >= 500 AND rownum <1000

Answer 2

Your table expression:

(SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) as foo

Has only one column - rownum - so when you select "*" from "foo" you get only the one column.

Your table expression needs to include all of the columns you want, just like e example you posted.