| Author |
syntax error in paging select
|
|
| Matteo Galletti 2005-04-20, 7:23 am |
| I'm trying to implement a paging select witch is a select that extracts a
subset of rows from a table specifying the pagesize and the pagenumber that
I want to show.
The example is this but the technique can be used also for general purpose:
SELECT TOP 10 COD_PERIF, NOME_IMPIANTO
FROM PERIF_ANAG
WHERE COD_PERIF NOT IN
(SELECT TOP 20 COD_PERIF FROM PERIF_ANAG ORDER BY NOME_IMPIANTO)
ORDER BY NOME_IMPIANTO;
There are two costants 10 and 20.
10 (the 1st constant) is the pagesize. This means that I choose to do a
paging select using pages large 10 rows.
20 (the 2nd constant) is a value calculated like that:
@PageSize * (@PageNumber -1)
Consider @PageSize = 10 and @PageNumber = 3 (I want to show the 3rd page).
The result is 10 * (3-1) = 10 * 2 = 20
If I use this SQL statement in Acces or SQL Server it works perfectly but
using it in SQL Anywhere 8 I get a syntax error. The message is:
"Error at line 4"
"Syntax error near 'ORDER' on line 4
I thought it was a syntax bug of SQL Anywhere so I tried to download the
upgrade of SQL Anywhere 8 but the error persists.
Is there anyone that knows the solution?
Thanks in advance
Magallo
| |
| Rob Waywell 2005-04-20, 9:23 am |
| Try this:
SELECT TOP 10 start at 20 COD_PERIF, NOME_IMPIANTO
FROM PERIF_ANAG
ORDER BY NOME_IMPIANTO;
or for an example using the asademo.db:
select top 10 start at 20 * from customer order by id
ASA SQL Reference
SQL Statements
SELECT statement
....
row-limitation :
FIRST | TOP n [ START AT m ]
....
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Matteo Galletti" <m.galletti@eldes.it> wrote in message
news:42661be1$1@foru
ms-1-dub...
> I'm trying to implement a paging select witch is a select that extracts a
> subset of rows from a table specifying the pagesize and the pagenumber
that
> I want to show.
>
> The example is this but the technique can be used also for general
purpose:
>
> SELECT TOP 10 COD_PERIF, NOME_IMPIANTO
> FROM PERIF_ANAG
> WHERE COD_PERIF NOT IN
> (SELECT TOP 20 COD_PERIF FROM PERIF_ANAG ORDER BY NOME_IMPIANTO)
> ORDER BY NOME_IMPIANTO;
>
> There are two costants 10 and 20.
>
> 10 (the 1st constant) is the pagesize. This means that I choose to do a
> paging select using pages large 10 rows.
> 20 (the 2nd constant) is a value calculated like that:
> @PageSize * (@PageNumber -1)
>
> Consider @PageSize = 10 and @PageNumber = 3 (I want to show the 3rd page).
> The result is 10 * (3-1) = 10 * 2 = 20
>
> If I use this SQL statement in Acces or SQL Server it works perfectly but
> using it in SQL Anywhere 8 I get a syntax error. The message is:
> "Error at line 4"
> "Syntax error near 'ORDER' on line 4
>
> I thought it was a syntax bug of SQL Anywhere so I tried to download the
> upgrade of SQL Anywhere 8 but the error persists.
>
> Is there anyone that knows the solution?
>
> Thanks in advance
> Magallo
>
>
| |
| Chris Keating \(iAnywhere Solutions\) 2005-04-20, 9:23 am |
| START AT is introduced in ASA9.
"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:426650e1@forums
-1-dub...
> Try this:
>
> SELECT TOP 10 start at 20 COD_PERIF, NOME_IMPIANTO
> FROM PERIF_ANAG
> ORDER BY NOME_IMPIANTO;
>
> or for an example using the asademo.db:
> select top 10 start at 20 * from customer order by id
>
>
> ASA SQL Reference
>
> SQL Statements
>
> SELECT statement
> ...
>
> row-limitation :
> FIRST | TOP n [ START AT m ]
> ...
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "Matteo Galletti" <m.galletti@eldes.it> wrote in message
> news:42661be1$1@foru
ms-1-dub...
> that
> purpose:
>
>
| |
| Matteo Galletti 2005-04-20, 9:23 am |
| Thanks for the response.
So, is there a way to do what I mean in ASA8?
Magallo
----------------------------------------------------------------------------------------------------
"Chris Keating (iAnywhere Solutions)" < Spam_NoThanks_keatin
g@iAnywhere.com>
ha scritto nel messaggio news:42665524$1@foru
ms-1-dub...
> START AT is introduced in ASA9.
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:426650e1@forums
-1-dub...
>
>
| |
| Ani Nica 2005-04-20, 8:23 pm |
| Your query will work as expected starting with the version ASA 9. ORDER BY
clause in the subselects, subqueries, and derived tables are not supported
in ASA 8 or earlier versions.
--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Matteo Galletti" <m.galletti@eldes.it> wrote in message
news:42661be1$1@foru
ms-1-dub...
> I'm trying to implement a paging select witch is a select that extracts a
> subset of rows from a table specifying the pagesize and the pagenumber
that
> I want to show.
>
> The example is this but the technique can be used also for general
purpose:
>
> SELECT TOP 10 COD_PERIF, NOME_IMPIANTO
> FROM PERIF_ANAG
> WHERE COD_PERIF NOT IN
> (SELECT TOP 20 COD_PERIF FROM PERIF_ANAG ORDER BY NOME_IMPIANTO)
> ORDER BY NOME_IMPIANTO;
>
> There are two costants 10 and 20.
>
> 10 (the 1st constant) is the pagesize. This means that I choose to do a
> paging select using pages large 10 rows.
> 20 (the 2nd constant) is a value calculated like that:
> @PageSize * (@PageNumber -1)
>
> Consider @PageSize = 10 and @PageNumber = 3 (I want to show the 3rd page).
> The result is 10 * (3-1) = 10 * 2 = 20
>
> If I use this SQL statement in Acces or SQL Server it works perfectly but
> using it in SQL Anywhere 8 I get a syntax error. The message is:
> "Error at line 4"
> "Syntax error near 'ORDER' on line 4
>
> I thought it was a syntax bug of SQL Anywhere so I tried to download the
> upgrade of SQL Anywhere 8 but the error persists.
>
> Is there anyone that knows the solution?
>
> Thanks in advance
> Magallo
>
>
| |
| Rob Waywell 2005-04-21, 8:23 pm |
| I knew I should have double checked the new features. :-(
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Chris Keating (iAnywhere Solutions)" < Spam_NoThanks_keatin
g@iAnywhere.com>
wrote in message news:42665524$1@foru
ms-1-dub...
> START AT is introduced in ASA9.
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:426650e1@forums
-1-dub...
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug[color=darkred]
a[color=darkred]
but[color=darkred]
the[color=darkred]
>
>
|
|
|
|