List:General Discussion« Previous MessageNext Message »
From:Anoop kumar V Date:August 22 2009 1:06am
Subject:Select clause using from and to (like rownum in Oracle)
View as plain text  
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 <anoopkumarv@stripped>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@stripped> 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@stripped> 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
>>
>>
>>
>>
>

Thread
Select clause using from and to (like rownum in Oracle)Anoop kumar V22 Aug