U++ forum: Welcome to the forum

Re: SqlArray Limit and Offset [message #7309 is a reply to message #7297]

Thu, 21 December 2006 15:17

fabio
Messages: 9
Registered: November 2006

Promising Member

The 2 level query work only for first time....

the solution is (from http://www.oracle.com/technology/oramag/oracle/06-sep/o56ask tom.html)

... 3 level select.....

select * from ( select a.*, ROWNUM as row_num from ( 
select .......... from .... where ...... order by ......
) a where rownum <= offset+limit) where row_num > offset

Upp working solution is ...

1) Change in file: upp\uppsrc\sql\SqlCtrl\SqlArray.cpp

SqlArray::AppendQuery() method:

sql * ::Select(cols).From(table).Where(wh).OrderBy(orderby).Limit(count).Offset(offset);

in:

sql * ::Select(cols).From(table).Where(wh).OrderBy(orderby).Limit(offset,count);

2) Change in file: upp\uppsrc\sql\Sql\SqlStatement.cpp

SqlSelect& SqlSelect::Limit(int64 offset, int limit) {

    text = SqlCase(ORACLE,"select * from ( select rownum_a.*, ROWNUM as row_num from ( ")() + text +
           SqlCase(ORACLE,Format(") rownum_a where rownum <= %d ) where row_num > %d", offset+limit,offset))
                  (Format(" limit %d, %d",offset,limit));

	return *this;
}

this query use rownum reserved word, row_num, rownum_a user word and generate an extra column row_num in the result.

3) optional ?
Remove SqlSelect::Limit(count)
Remove SqlSelect::Offset(offset)
for complete portability

Tested in oracle and sqlite, my be work in MySql (same syntax of sqlite).

Fabio

Attachment: limit.zip
(Size: 3.91KB, Downloaded 334 times)

Report message to a moderator