Is there anyway the SELECT query can be forced to use the "from" and "to"
rownum parameters?
1st LIMIT arg = OracleFromArg
2nd LIMIT arg = OracleToArg - OracleFromArg + 1
so 'from 11 to 20' becomes LIMIT 11,10.
PB
-----
Anoop kumar V wrote:
Hi All,
I am facing a problem in porting an application written for oracle to run on
mysql.
The application uses a sqlmap (ibatis) at the heart of which is basically a
file that defines all sql's used in the application. It is very well
organized this way. The application uses Oracle as the database. The problem
is that for pagination purposes the sql's written use rownum and accept 2
arguments - the "from" rownum and the "to" rownum.
I am trying to run the same application on my laptop that runs mysql. I have
migrated all data and all the sql queries work perfectly except the one that
use pagination and the rownum.
I know in mysql there is support for sql using the LIMIT clause, but the
LIMIT seems to take 2 arguments, the first one being the start rownum and
the second being the number of rows to output. I need the second to be the
"to" rownum. I have done a lot of googling, but apart from just putting a
rownum for the sql output there was no real usages for pagination purposes.
I cannot use the LIMIT as it is in mysql, because that would mean I would
have to change the application logic which I do not want to do. I also do
not want to install Oracle on my laptop, just too heavy.
I have found this to work except I am not sure how to pass a where clause
for the rownum part:
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t
I was trying something like:
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum between 10, 20;
or even
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum=1;
I get the error:
ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause'
Is there anyway the SELECT query can be forced to use the "from" and "to"
rownum parameters?
Thanks a lot for any help,
Anoop
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09 06:04:00
