Select clause using from and to (like rownum in Oracle)

--0016e65b6176c51c9b0471ae3107
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--0016e65b6176c51c9b0471ae3107--

Re: Select clause using from and to (like rownum in Oracle)

--------------080604030505030006040006
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>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
>
>

--------------080604030505030006040006--

Select clause using from and to (like rownum in Oracle)

--0016e65b62a68c627b0471b09934
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Never mind. I got it to work..

I had to really trim down the entire statement:

set @sql = concat( "select
iams_id as iamsId
,division_name as divisionName
,region_name as regionName
,isactive as isActive
from user_approvers
limit ", #from#, ",", (#from#-#to#+1) );
prepare stmt from @sql;
execute stmt;
drop prepare stmt;


But I am not able to use it as a sqlmapped statement in iBatis, but that is
a separate problem for a different user list.. but you gave me the idea so
far and it works. Thanks very much.

Thanks,
Anoop



On Fri, Aug 21, 2009 at 8:26 PM, Anoop kumar V wrote:

> I am having trouble executing what you have sent. Below is output....
>
> mysql> set @sql = concat( "select
> "> iams_id as iamsId
> "> ,division_name as divisionName
> "> ,region_name as regionName
> "> ,isactive as isActive
> "> from (
> "> select
> "> iams_id
> "> ,division_name
> "> ,region_name
> "> ,isactive
> "> from user_approvers )
> "> order by rn limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1248 (42000): Every derived table must have its own alias
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql> set @sql = concat( "select
> "> iams_id as iamsId
> "> ,division_name as divisionName
> "> ,region_name as regionName
> "> ,isactive as isActive
> "> from (
> "> select
> "> iams_id
> "> ,division_name
> "> ,region_name
> "> ,isactive
> "> from user_approvers ) a
> "> order by rn limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1054 (42S22): Unknown column 'rn' in 'order clause'
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql> set @sql = concat( "select
> "> iams_id as iamsId
> "> ,division_name as divisionName
> "> ,region_name as regionName
> "> ,isactive as isActive
> "> from (
> "> select
> "> iams_id
> "> ,division_name
> "> ,region_name
> "> ,isactive
> "> from user_approvers ) a
> "> limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'limit 10,11' at line 13
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql>
>
> Thanks,
> Anoop
>
>
>
> On Fri, Aug 21, 2009 at 7:22 PM, Peter Brawley <
> peter.brawley@earthlink.net> wrote:
>
>> I think you'd need to use Prepare, eg replace the query with ...
>>
>> set @sql = concat( "select
>> user_id as iamsId
>> ,division_name as divisionName
>> ,region_name as regionName
>> ,isactive as isActive
>> from (
>> select
>> user_id
>> ,division_name
>> ,region_name
>> ,isactive
>> from user_approvers )
>> order by rn limit ", #from, ",", (#to-#from+1) );
>> prepare stmt from @sql;
>> execute stmt;
>> drop prepare stmt;
>>
>>
>> PB
>>
>> -----
>>
>> Anoop kumar V wrote:
>>
>> Thanks very much Peter.
>>
>> But I think I did figure that much. What I am lacking is the integration
>> of that logic into the sql.
>>
>> The current sql (made for oracle) is like this - I can change it all I
>> want because of the sql map which is configurable...
>>
>> select
>> user_id as iamsId
>> ,division_name as divisionName
>> ,region_name as regionName
>> ,isactive as isActive
>> from (
>> select
>> user_id
>> ,division_name
>> ,region_name
>> ,isactive
>> ,row_number() over (order by division_name, region_name) rn
>> from user_approvers )
>> where rn between #from# and #to#
>> order by rn
>>
>> I can change everything but the parameters to the sql: #from# and #to#.
>> These come from the application logic and is user enterred (not directly,
>> but through pagination etc - you get the idea)
>>
>> I tried things like the following (to get rows from 11 to 20):
>> select * from user_approvers limit 10, 20-10;
>>
>> Also tried assigning variables.. still no go.
>>
>> Thanks,
>> Anoop
>>
>>
>> On Fri, Aug 21, 2009 at 6:44 PM, Peter Brawley <
>> peter.brawley@earthlink.net> wrote:
>>
>>> >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
>>>
>>>
>>>
>>>
>> ------------------------------
>>
>>
>> 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
>>
>>
>>
>>
>

--0016e65b62a68c627b0471b09934--