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.

Got 'set ROWCOUNT' well what about 'set SKIPROW'

10 posts in Product Futures Discussion Last posting was on 2002-09-11 23:07:47.0Z
JohnLon Posted on 2002-06-08 00:10:51.0Z
From: "JohnLon" <jljunk@hotmail.com>
Subject: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Date: Sat, 8 Jun 2002 01:10:51 +0100
Lines: 6
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <P6zwdKoDCHA.201@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: pc-80-193-216-27-hw.blueyonder.co.uk 80.193.216.27
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:463
Article PK: 93633

Handy for those queries where you want to skip the first N rows of potential
results.

eg for "paging" results in web apps


Iwish Posted on 2002-06-12 01:49:42.0Z
From: "Iwish" <iwish@example.com>
References: <P6zwdKoDCHA.201@forums.sybase.com>
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Date: Wed, 12 Jun 2002 10:49:42 +0900
Lines: 63
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <2$yWmbbECHA.200@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 158.77.52.194
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:456
Article PK: 93625

Hi,

i entirely agree with your set skiprow option.
i summarize how to select portion of data in other dbms.

please sybase recognize why ase way is uncovenient and this feature should
be implement in ase.

============================================================
[oracle] : select a.*
from (select rownum, * from table ) a
where a.rownum >= 5 and a.rownum <=10

[db2] : select t.yyt , t.*
from ( select *, rownumber() over (order by empno) as yyt from
employee) as t
where yyt between 5 and 10


[ms sql] : select top 10 * from table <= only support top function, but
ms sql support scrollable cursor can skip, go backward & forward

[my sql] : select * from table LIMIT 5,10 <= really nice and simple way

[informix]: select first 10 * from table <= as i know, informix only
support first function
=============================================================
[sybase]: set rowcount 10
select rownum=identity(5,0), * into#temptable from table

set rowcount 5
select * from #temptable
order by rownum desc

set rowcount 0

drop table #temptable

<= many steps are required.
if you use was(web application server) or tp-monitor,
you should always drop #temptable after selecting data.
and it caues unnecessary disk io. in worst case it can
make a big temp table if you want to select last page.

==============================================================
<set rowcount 5 skiprow 5>
if this syntax can be used in ase, many steps will be implemented in 1
steps.

Thanks.

"JohnLon" <jljunk@hotmail.com> wrote in message
news:P6zwdKoDCHA.201@forums.sybase.com...
> Handy for those queries where you want to skip the first N rows of
potential
> results.
>
> eg for "paging" results in web apps
>
>


Pablo Sanchez Posted on 2002-06-12 03:15:28.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com>
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Date: Tue, 11 Jun 2002 21:15:28 -0600
Lines: 89
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <HSBAcDcECHA.200@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:455
Article PK: 93623

In order to implement paging, I wouldn't implement any of the listed
solutions ... the performance would be very poor. It's best to
create a list of ID's that the front-end passes to the back-end. The
back-end returns that set to the front-end. The ID's are kept in a
temporary table so the front-end can tell the back-end, return page 3,
page 2, page 3, page 4, etc.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts

"Iwish" <iwish@example.com> wrote in message
news:2$yWmbbECHA.200@forums.sybase.com...
> Hi,
>
> i entirely agree with your set skiprow option.
> i summarize how to select portion of data in other dbms.
>
> please sybase recognize why ase way is uncovenient and this feature
should
> be implement in ase.
>
> ============================================================
> [oracle] : select a.*
> from (select rownum, * from table ) a
> where a.rownum >= 5 and a.rownum <=10
>
> [db2] : select t.yyt , t.*
> from ( select *, rownumber() over (order by empno) as yyt
from
> employee) as t
> where yyt between 5 and 10
>
>
> [ms sql] : select top 10 * from table <= only support top
function, but
> ms sql support scrollable cursor can skip, go backward & forward
>
> [my sql] : select * from table LIMIT 5,10 <= really nice and
simple way
>
> [informix]: select first 10 * from table <= as i know, informix
only
> support first function
> =============================================================
> [sybase]: set rowcount 10
> select rownum=identity(5,0), * into#temptable from
table
>
> set rowcount 5
> select * from #temptable
> order by rownum desc
>
> set rowcount 0
>
> drop table #temptable
>
> <= many steps are required.
> if you use was(web application server) or
tp-monitor,
> you should always drop #temptable after selecting data.
> and it caues unnecessary disk io. in worst
case it can
> make a big temp table if you want to select last page.
>
> ==============================================================
> <set rowcount 5 skiprow 5>
> if this syntax can be used in ase, many steps will be implemented in
1
> steps.
>
> Thanks.
>
>
> "JohnLon" <jljunk@hotmail.com> wrote in message
> news:P6zwdKoDCHA.201@forums.sybase.com...
> > Handy for those queries where you want to skip the first N rows of
> potential
> > results.
> >
> > eg for "paging" results in web apps
> >
> >
>
>


Brien2k Posted on 2002-09-11 22:56:54.0Z
From: Brien2k
Date: Wed, 11 Sep 2002 18:56:54 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Message-ID: <D7C8F22882556B21007E0F5D85256C31.0016F9AF85256BD6@webforums>
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com>
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:304
Article PK: 93475

To only select a specific amount of rows...

Syntax:
SELECT (payload) FROM (tables) FETCH FIRST (records) ROW ONLY

See example:
SELECT * FROM USER.RATES FETCH FIRST 3 ROW ONLY


Pablo Sanchez Posted on 2002-09-11 23:07:47.0Z
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
From: Pablo Sanchez <pablo@dev.null>
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com> <D7C8F22882556B21007E0F5D85256C31.0016F9AF85256BD6@webforums>
Organization: High-Performance Database Engineering
Message-ID: <Xns9286ABED8995Apingottpingottbah@199.93.177.77>
User-Agent: Xnews/5.04.25
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 11 Sep 2002 19:07:47 -0400
Lines: 18
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:303
Article PK: 93473

Brien2k wrote in news:D7C8F22882556B21007E0F5D85256C31.0016F9AF85256BD6
@webforums:

> To only select a specific amount of rows...
>
> Syntax:
> SELECT (payload) FROM (tables) FETCH FIRST (records) ROW ONLY
>
> See example:
> SELECT * FROM USER.RATES FETCH FIRST 3 ROW ONLY
>

Howdy!

Have you had a chance to see the 'rowcount' parm?

set rowcount 3
SELECT * FROM USER.RATES

Does that cover what you're seeking?

For more info on 'rowcount', see -- you'll have to scroll down the
page:

<http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/
@Generic__BookTextView/72119;hf=0#X>
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com


Michael Peppler Posted on 2002-06-12 17:43:51.0Z
Message-ID: <3D078857.DA9CB9AC@peppler.org>
Date: Wed, 12 Jun 2002 10:43:51 -0700
From: Michael Peppler <mpeppler@peppler.org>
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.18 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 13
NNTP-Posting-Host: sl5.cmates.com 208.23.213.5
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:451
Article PK: 93621


Pablo Sanchez wrote:
>
> In order to implement paging, I wouldn't implement any of the listed
> solutions ... the performance would be very poor. It's best to
> create a list of ID's that the front-end passes to the back-end. The
> back-end returns that set to the front-end. The ID's are kept in a
> temporary table so the front-end can tell the back-end, return page 3,
> page 2, page 3, page 4, etc.

Absolutely.

Or use key based where clauses.

Michael


Sherlock, Kevin Posted on 2002-06-12 20:15:59.0Z
Message-ID: <3D07ABE1.59D67913@qwest.com.nospam>
Date: Wed, 12 Jun 2002 15:15:59 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com> <3D078857.DA9CB9AC@peppler.org>
Content-Type: multipart/mixed; boundary="------------819518659EA3849885A713CA"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 46
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:450
Article PK: 93619


Michael Peppler wrote:
>
> Pablo Sanchez wrote:
> >
> > In order to implement paging, I wouldn't implement any of the listed
> > solutions ... the performance would be very poor. It's best to
> > create a list of ID's that the front-end passes to the back-end. The
> > back-end returns that set to the front-end. The ID's are kept in a
> > temporary table so the front-end can tell the back-end, return page 3,
> > page 2, page 3, page 4, etc.
>
> Absolutely.
>
> Or use key based where clauses.
>
> Michael

key based where clauses are the way to go for single column keys, but
when you get into composit keys, things aren't so nice.

I would like to see support for composite predicates (like Tandem's
NonStop-SQL supports for instance).

Example:

select manager,name,ssn,dept,salary
from emp
where (ssn,name,dept) > ("123456789","Doe",1001)


Instead of:

select manager,name,ssn,dept,salary
from emp
where ssn > "123456789"
or (ssn = "123456789" and name > "Doe")
or (ssn = "123456789" and name = "Doe" and dept > 1001)


Download VCard ksherlo.vcf


Pablo Sanchez Posted on 2002-06-13 14:34:42.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com> <3D078857.DA9CB9AC@peppler.org> <3D07ABE1.59D67913@qwest.com.nospam>
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Date: Thu, 13 Jun 2002 08:34:42 -0600
Lines: 36
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <NqvrrjuECHA.70@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:449
Article PK: 93618


"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3D07ABE1.59D67913@qwest.com.nospam...
> Michael Peppler wrote:
> >
> > Pablo Sanchez wrote:
> > >
> > > In order to implement paging, I wouldn't implement any of the
listed
> > > solutions ... the performance would be very poor. It's best to
> > > create a list of ID's that the front-end passes to the back-end.
The
> > > back-end returns that set to the front-end. The ID's are kept
in a
> > > temporary table so the front-end can tell the back-end, return
page 3,
> > > page 2, page 3, page 4, etc.
> >
> > Absolutely.
> >
> > Or use key based where clauses.
> >
> > Michael
>
> key based where clauses are the way to go for single column keys,
but
> when you get into composit keys, things aren't so nice.

I try to use surrogate keys which eliminates this issue.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts


Sherlock, Kevin Posted on 2002-06-13 20:19:15.0Z
Message-ID: <3D08FE29.FC157FC2@qwest.com.nospam>
Date: Thu, 13 Jun 2002 15:19:15 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com> <3D078857.DA9CB9AC@peppler.org> <3D07ABE1.59D67913@qwest.com.nospam> <NqvrrjuECHA.70@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------CB2A8379319A6DDCA78D6F67"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 51
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:447
Article PK: 93615


Pablo Sanchez wrote:
>
> "Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
> news:3D07ABE1.59D67913@qwest.com.nospam...
> > Michael Peppler wrote:
> > >
> > > Pablo Sanchez wrote:
> > > >
> > > > In order to implement paging, I wouldn't implement any of the
> listed
> > > > solutions ... the performance would be very poor. It's best to
> > > > create a list of ID's that the front-end passes to the back-end.
> The
> > > > back-end returns that set to the front-end. The ID's are kept
> in a
> > > > temporary table so the front-end can tell the back-end, return
> page 3,
> > > > page 2, page 3, page 4, etc.
> > >
> > > Absolutely.
> > >
> > > Or use key based where clauses.
> > >
> > > Michael
> >
> > key based where clauses are the way to go for single column keys,
> but
> > when you get into composit keys, things aren't so nice.
>
> I try to use surrogate keys which eliminates this issue.

What issue? I already stated that single column keys aren't a problem.
Surrogate keys don't necessarily translate to single column keys, and
certainly are not a solution that fits every situation. Composite
predicates are what I'm suggesting, which makes surrogate keys
unecessary and allows a more flexible indexing approach.


Download VCard ksherlo.vcf


Pablo Sanchez Posted on 2002-06-14 12:51:26.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <P6zwdKoDCHA.201@forums.sybase.com> <2$yWmbbECHA.200@forums.sybase.com> <HSBAcDcECHA.200@forums.sybase.com> <3D078857.DA9CB9AC@peppler.org> <3D07ABE1.59D67913@qwest.com.nospam> <NqvrrjuECHA.70@forums.sybase.com> <3D08FE29.FC157FC2@qwest.com.nospam>
Subject: Re: Got 'set ROWCOUNT' well what about 'set SKIPROW'
Date: Fri, 14 Jun 2002 06:51:26 -0600
Lines: 42
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <0D6CqO6ECHA.196@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:442
Article PK: 93613


"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3D08FE29.FC157FC2@qwest.com.nospam...
> Pablo Sanchez wrote:
> >
> > > > Absolutely.
> > > >
> > > > Or use key based where clauses.
> > > >
> > > > Michael
> > >
> > > key based where clauses are the way to go for single column
keys,
> > but
> > > when you get into composit keys, things aren't so nice.
> >
> > I try to use surrogate keys which eliminates this issue.
>
> What issue? I already stated that single column keys aren't a
problem.
> Surrogate keys don't necessarily translate to single column keys,
and
> certainly are not a solution that fits every situation. Composite
> predicates are what I'm suggesting, which makes surrogate keys
> unecessary and allows a more flexible indexing approach.

I'm not entirely clear on what you are stating. Perhaps you can
elaborate.

What I'm suggesting is given a table with a surrogate key and its
natural key and any other keys (composite or not), they all map to a
single column surrogate key. Given that the single column surrogate
key exists, the user may logically request data via a key (composite
or not), which gets translated to a surrogate. Given this set of
unique values, one can page back and forth.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts