sql - join two columns from a table and achieve pagination

Question

I'm trying to join two columns along with pagination. I am able to achieve this without pagination using below query:

SELECT (column1 || ', '|| column2) des, (column3|| ''|| column4) id
FROM table
WHERE column1 IS NOT NULL
GROUP BY column1, column2, column3, column4
ORDER BY column1 ASC

For adding pagination to above query I have used below query but it is not working:

SELECT (column1 || ', '|| column2) des, (column3 || ''|| column4) id FROM (
     SELECT rownum rnum, a.*
     FROM ( 
        SELECT (column1 || ', '|| column2) des, (column3|| ''|| column4) id
        FROM table
        WHERE column1 IS NOT NULL
        GROUP BY column1, column2, column3, column4
        ORDER BY column1 ASC) a
        WHERE rownum < ((1 * 100) + 1)
       )
WHERE rnum >= ((1 - 1) * 100) + 1

I am running these queries on oracle database. Can anyone help me to achieve this or if there is any syntax error in the above query.

Possible duplicate of [Best practice for pagination in Oracle?](http://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle) — are, Aug 16 '16 at 21:27
http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html — are, Aug 16 '16 at 21:27

score 0 · Accepted Answer · answered Aug 16 '16 at 21:20

You can't refer to tables or columns from tables in the subquery directly in your outer query. You can only see the projection from the inner query - that is the columns or aliases column expressions the subquery returns.

So you would do something like:

SELECT des, id FROM (
  SELECT rownum rnum, a.*
  FROM ( 
    SELECT column1 ||', '|| column2 des, column3 || column4 id
    FROM table
    WHERE column1 IS NOT NULL
    GROUP BY column1, column2, column3, column4
    ORDER BY column1 ASC
  ) a
  WHERE rownum < ((1 * 100) + 1)
)
WHERE rnum >= ((1 - 1) * 100) + 1

or:

SELECT des, id FROM (
  SELECT column1 || ', '|| column2 des, column3 || column4 id,
    rownum rnum
  FROM table
  WHERE column1 IS NOT NULL
  GROUP BY column1, column2, column3, column4
  ORDER BY column1 ASC
)
WHERE rnum < ((1 * 100) + 1)
AND rnum >= ((1 - 1) * 100) + 1

You can also use the row_number() function instead of rownum.

sql - join two columns from a table and achieve pagination

1 Answers1