Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Support for pagination (limiting resultsets)

3 posts in General Discussion Last posting was on 2010-10-12 20:49:02.0Z
Morten Andersen-Gott Posted on 2010-10-11 09:04:11.0Z
Sender: 480f.4cb2cf7b.1804289383@sybase.com
From: Morten Andersen-Gott
Newsgroups: sybase.public.ase.general
Subject: Support for pagination (limiting resultsets)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cb2d306.4885.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Oct 2010 02:04:11 -0700
X-Trace: forums-1-dub 1286787851 10.22.241.41 (11 Oct 2010 02:04:11 -0700)
X-Original-Trace: 11 Oct 2010 02:04:11 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29641
Article PK: 78871

There is a huge gap in Sybase's support for pagination.
Almost every
other DB vendor out there has some sort of support for
pagination
(http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows)
, the
most common of which is the use of windowing queries (part
of SQL 2003). (Other
implementations include the use of limit and offset, or top
and start at). The only way to get this in Sybase ASE is
through the use of cursors, which is a hassle to do through
the jdbc-
api. Are there any plans to support windowing queries?
Sybase Anywhere supports both windowing and top + start at,
why
isn't this feature included in the more expensive enterprise
product?

I have submitted a feature request with ID: 11633807 -
Implement support for window queries

Sybase ASE _does_ support "top", but that only helps when
you want row 0 to 100 and not when you want row 100 to 200
in a single query.


Bret Halford Posted on 2010-10-11 15:42:24.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Support for pagination (limiting resultsets)
References: <4cb2d306.4885.1681692777@sybase.com>
In-Reply-To: <4cb2d306.4885.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cb33060$1@forums-1-dub>
Date: 11 Oct 2010 08:42:24 -0700
X-Trace: forums-1-dub 1286811744 10.22.241.152 (11 Oct 2010 08:42:24 -0700)
X-Original-Trace: 11 Oct 2010 08:42:24 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29644
Article PK: 78874


On 10/11/2010 3:04 AM, Morten Andersen-Gott wrote:
> There is a huge gap in Sybase's support for pagination.
> Almost every
> other DB vendor out there has some sort of support for
> pagination
> (http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows)
> , the
> most common of which is the use of windowing queries (part
> of SQL 2003). (Other
> implementations include the use of limit and offset, or top
> and start at). The only way to get this in Sybase ASE is
> through the use of cursors, which is a hassle to do through
> the jdbc-
> api. Are there any plans to support windowing queries?
> Sybase Anywhere supports both windowing and top + start at,
> why
> isn't this feature included in the more expensive enterprise
> product?
>
> I have submitted a feature request with ID: 11633807 -
> Implement support for window queries
>
> Sybase ASE _does_ support "top", but that only helps when
> you want row 0 to 100 and not when you want row 100 to 200
> in a single query.

There are methods other than cursors.

You can select the result set into a temp table
including an identity column. Example:

select *, rowid = identity(9) into #foo from mytable

select * from #foo where rowid between 100 and 200


11633807 is actually the support case number rather than a feature
request number. CR 642018 is an open feature request for an "offset"
or "Start at" operator.


Morten Andersen-Gott Posted on 2010-10-12 20:49:02.0Z
Sender: 73e9.4cb4c5e9.1804289383@sybase.com
From: Morten Andersen-Gott
Newsgroups: sybase.public.ase.general
Subject: Re: Support for pagination (limiting resultsets)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cb4c9be.742c.1681692777@sybase.com>
References: <4cb33060$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Oct 2010 13:49:02 -0700
X-Trace: forums-1-dub 1286916542 10.22.241.41 (12 Oct 2010 13:49:02 -0700)
X-Original-Trace: 12 Oct 2010 13:49:02 -0700, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29647
Article PK: 78877


> There are methods other than cursors.
>
> You can select the result set into a temp table
> including an identity column. Example:
>
> select *, rowid = identity(9) into #foo from mytable
>
> select * from #foo where rowid between 100 and 200

Problem with that approach is that it must be executed as
two statements, which makes it difficult to use from a
simple JDBC statement.


> 11633807 is actually the support case number rather than a
> feature request number. CR 642018 is an open feature
> request for an "offset" or "Start at" operator.

Hmm. Cool, I'll follow that. I'd argue for the window
queries though, as those seem to be more common across
vendors. Still, at the moment I'd be happy with any approach
that would let me do paging with a single query execution.