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.

row_number rownumber function in ASE

7 posts in General Discussion Last posting was on 2012-05-18 22:35:41.0Z
tartampion Posted on 2012-05-17 15:48:34.0Z
Sender: 6169.4fb51bca.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: row_number rownumber function in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb51dd2.6199.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 May 2012 08:48:34 -0700
X-Trace: forums-1-dub 1337269714 172.20.134.41 (17 May 2012 08:48:34 -0700)
X-Original-Trace: 17 May 2012 08:48:34 -0700, 172.20.134.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31116
Article PK: 74004

Has anyone got a function (ASE 15.03 and above) which can
provide the row number in a select statement? I would
appreciate to have copy of it.
I know naturally that we can put the results of the select
statement into a temporary table with an identity column
and then select from the temporary table; but I am seeking
for a way to avoid the double select from the query to temp
table and then from the temp table.


Mark A. Parsons Posted on 2012-05-17 19:43:25.0Z
Sender: 608e.4fb51675.1804289383@sybase.com
From: Mark A. Parsons
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb554dd.696c.1681692777@sybase.com>
References: <4fb51dd2.6199.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 May 2012 12:43:25 -0700
X-Trace: forums-1-dub 1337283805 172.20.134.41 (17 May 2012 12:43:25 -0700)
X-Original-Trace: 17 May 2012 12:43:25 -0700, 172.20.134.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31118
Article PK: 74009

You'll have to have a way of passing the last/next row
number between result set rows (eg, use an application
context function aka ACF), and you'll need to supply a
result set column/value as an argument to the function to
insure it fires for each line.

With these requirements in mind, just off the top of my head
..

======================
create function row_number
(@placeholder int) --change datatype based on input column's
type
returns smallint
as
declare @rn smallint, @dummy int
-- get last value and add 1
select
@rn=isnull(convert(smallint,get_appcontext('row','number')),0)+1
-- update ACF with new value
select @dummy=0 * rm_appcontext('row','number') +
set_appcontext('row','number',convert(varchar,@rn))
return @rn
======================

Before your query you'll need to clear the ACF, eg:

======================
declare @dummy int
select @dummy=rm_appcontext('row','number')

select dbo.row_number(id),id,name
from sysobjects
-- order by name
======================

> Has anyone got a function (ASE 15.03 and above) which can
> provide the row number in a select statement? I would
> appreciate to have copy of it.
> I know naturally that we can put the results of the select
> statement into a temporary table with an identity column
> and then select from the temporary table; but I am seeking
> for a way to avoid the double select from the query to
> temp table and then from the temp table.


tartampion Posted on 2012-05-18 13:39:15.0Z
Sender: 12e1.4fb64fc9.1804289383@sybase.com
From: Tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb65103.1319.1681692777@sybase.com>
References: <4fb554dd.696c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 May 2012 06:39:15 -0700
X-Trace: forums-1-dub 1337348355 172.20.134.41 (18 May 2012 06:39:15 -0700)
X-Original-Trace: 18 May 2012 06:39:15 -0700, 172.20.134.41
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31124
Article PK: 74010

Mark, we shall use your proposition thankfully.

> You'll have to have a way of passing the last/next row
> number between result set rows (eg, use an application
> context function aka ACF), and you'll need to supply a
> result set column/value as an argument to the function to
> insure it fires for each line.
>
> With these requirements in mind, just off the top of my
> head ...
>
> ======================
> create function row_number
> (@placeholder int) --change datatype based on input
> column's type
> returns smallint
> as
> declare @rn smallint, @dummy int
> -- get last value and add 1
> select
> @rn=isnull(convert(smallint,get_appcontext('row'
> ,'number')),0)+1 -- update ACF with new value
> select @dummy=0 * rm_appcontext('row','number') +
> set_appcontext('row','number',convert(varchar
> ,@rn)) return @rn
> ======================
>
> Before your query you'll need to clear the ACF, eg:
>
> ======================
> declare @dummy int
> select @dummy=rm_appcontext('row','number')
>
> select dbo.row_number(id),id,name
> from sysobjects
> -- order by name
> ======================
>
>
> > Has anyone got a function (ASE 15.03 and above) which
> > can provide the row number in a select statement? I
> > would appreciate to have copy of it.
> > I know naturally that we can put the results of the
> > select statement into a temporary table with an
> > identity column and then select from the temporary table
> > ; but I am seeking for a way to avoid the double select
> > from the query to temp table and then from the temp
> table.


Bret Halford Posted on 2012-05-18 17:00:03.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
References: <4fb554dd.696c.1681692777@sybase.com> <4fb65103.1319.1681692777@sybase.com>
In-Reply-To: <4fb65103.1319.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: <4fb68013$1@forums-1-dub>
Date: 18 May 2012 10:00:03 -0700
X-Trace: forums-1-dub 1337360403 10.22.241.152 (18 May 2012 10:00:03 -0700)
X-Original-Trace: 18 May 2012 10:00:03 -0700, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31127
Article PK: 74016

While it is a clever solution, do test against the simple
"select into a temp table with identity() and reselect
the rows from the temp table" approach - the performance
of that is probably much better than the overhead of the
UDF.

-bret

On 5/18/2012 7:39 AM, Tartampion wrote:
> Mark, we shall use your proposition thankfully.
>
>> You'll have to have a way of passing the last/next row
>> number between result set rows (eg, use an application
>> context function aka ACF), and you'll need to supply a
>> result set column/value as an argument to the function to
>> insure it fires for each line.
>>
>> With these requirements in mind, just off the top of my
>> head ...
>>
>> ======================
>> create function row_number
>> (@placeholder int) --change datatype based on input
>> column's type
>> returns smallint
>> as
>> declare @rn smallint, @dummy int
>> -- get last value and add 1
>> select
>> @rn=isnull(convert(smallint,get_appcontext('row'
>> ,'number')),0)+1 -- update ACF with new value
>> select @dummy=0 * rm_appcontext('row','number') +
>> set_appcontext('row','number',convert(varchar
>> ,@rn)) return @rn
>> ======================
>>
>> Before your query you'll need to clear the ACF, eg:
>>
>> ======================
>> declare @dummy int
>> select @dummy=rm_appcontext('row','number')
>>
>> select dbo.row_number(id),id,name
>> from sysobjects
>> -- order by name
>> ======================
>>
>>
>>> Has anyone got a function (ASE 15.03 and above) which
>>> can provide the row number in a select statement? I
>>> would appreciate to have copy of it.
>>> I know naturally that we can put the results of the
>>> select statement into a temporary table with an
>>> identity column and then select from the temporary table
>>> ; but I am seeking for a way to avoid the double select
>>> from the query to temp table and then from the temp
>> table.


"Mark A. Parsons" <iron_horse Posted on 2012-05-18 22:35:41.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
References: <4fb554dd.696c.1681692777@sybase.com> <4fb65103.1319.1681692777@sybase.com> <4fb68013$1@forums-1-dub>
In-Reply-To: <4fb68013$1@forums-1-dub>
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: <4fb6cebd$1@forums-1-dub>
Date: 18 May 2012 15:35:41 -0700
X-Trace: forums-1-dub 1337380541 10.22.241.152 (18 May 2012 15:35:41 -0700)
X-Original-Trace: 18 May 2012 15:35:41 -0700, vip152.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31128
Article PK: 74018

Agreed, UDF overhead can be quite noticeable for large volumes of UDF calls.

'course, the logic of the UDF could be pulled out and placed directly in the main query, thus eliminating the UDF
overhead and skipping the overhead of the #temp table, but at the cost of adding complexity to the query with all of the
embedded ACF calls (not to mention the hassles of granted SELECT permissions to the ACF functions) ... decisions,
decisions, decisions ...

On 05/18/2012 11:00, Bret Halford wrote:
> While it is a clever solution, do test against the simple
> "select into a temp table with identity() and reselect
> the rows from the temp table" approach - the performance
> of that is probably much better than the overhead of the
> UDF.
>
> -bret
>
>
> On 5/18/2012 7:39 AM, Tartampion wrote:
>> Mark, we shall use your proposition thankfully.
>>
>>> You'll have to have a way of passing the last/next row
>>> number between result set rows (eg, use an application
>>> context function aka ACF), and you'll need to supply a
>>> result set column/value as an argument to the function to
>>> insure it fires for each line.
>>>
>>> With these requirements in mind, just off the top of my
>>> head ...
>>>
>>> ======================
>>> create function row_number
>>> (@placeholder int) --change datatype based on input
>>> column's type
>>> returns smallint
>>> as
>>> declare @rn smallint, @dummy int
>>> -- get last value and add 1
>>> select
>>> @rn=isnull(convert(smallint,get_appcontext('row'
>>> ,'number')),0)+1 -- update ACF with new value
>>> select @dummy=0 * rm_appcontext('row','number') +
>>> set_appcontext('row','number',convert(varchar
>>> ,@rn)) return @rn
>>> ======================
>>>
>>> Before your query you'll need to clear the ACF, eg:
>>>
>>> ======================
>>> declare @dummy int
>>> select @dummy=rm_appcontext('row','number')
>>>
>>> select dbo.row_number(id),id,name
>>> from sysobjects
>>> -- order by name
>>> ======================
>>>
>>>
>>>> Has anyone got a function (ASE 15.03 and above) which
>>>> can provide the row number in a select statement? I
>>>> would appreciate to have copy of it.
>>>> I know naturally that we can put the results of the
>>>> select statement into a temporary table with an
>>>> identity column and then select from the temporary table
>>>> ; but I am seeking for a way to avoid the double select
>>>> from the query to temp table and then from the temp
>>> table.
>


Eisen Posted on 2012-05-18 03:06:46.0Z
Sender: 5413.4fb4bf53.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb5bcc6.78b0.1681692777@sybase.com>
References: <4fb554dd.696c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 May 2012 20:06:46 -0700
X-Trace: forums-1-dub 1337310406 172.20.134.41 (17 May 2012 20:06:46 -0700)
X-Original-Trace: 17 May 2012 20:06:46 -0700, 172.20.134.41
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31121
Article PK: 74011

Or maybe you can use "select @@rowcount" right after your
"select" statement directly

Best Regards
Eisen

> You'll have to have a way of passing the last/next row
> number between result set rows (eg, use an application
> context function aka ACF), and you'll need to supply a
> result set column/value as an argument to the function to
> insure it fires for each line.
>
> With these requirements in mind, just off the top of my
> head ...
>
> ======================
> create function row_number
> (@placeholder int) --change datatype based on input
> column's type
> returns smallint
> as
> declare @rn smallint, @dummy int
> -- get last value and add 1
> select
> @rn=isnull(convert(smallint,get_appcontext('row'
> ,'number')),0)+1 -- update ACF with new value
> select @dummy=0 * rm_appcontext('row','number') +
> set_appcontext('row','number',convert(varchar
> ,@rn)) return @rn
> ======================
>
> Before your query you'll need to clear the ACF, eg:
>
> ======================
> declare @dummy int
> select @dummy=rm_appcontext('row','number')
>
> select dbo.row_number(id),id,name
> from sysobjects
> -- order by name
> ======================
>
>
> > Has anyone got a function (ASE 15.03 and above) which
> > can provide the row number in a select statement? I
> > would appreciate to have copy of it.
> > I know naturally that we can put the results of the
> > select statement into a temporary table with an
> > identity column and then select from the temporary table
> > ; but I am seeking for a way to avoid the double select
> > from the query to temp table and then from the temp
> table.


tartampion Posted on 2012-05-18 13:45:08.0Z
Sender: 12e1.4fb64fc9.1804289383@sybase.com
From: Tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: row_number rownumber function in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb65264.134d.1681692777@sybase.com>
References: <4fb5bcc6.78b0.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 May 2012 06:45:08 -0700
X-Trace: forums-1-dub 1337348708 172.20.134.41 (18 May 2012 06:45:08 -0700)
X-Original-Trace: 18 May 2012 06:45:08 -0700, 172.20.134.41
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31125
Article PK: 74013

Thanks Eisen,
I have most probably not clearly expressed myself, I would
like to have row number associated with each row, we all
know that the select @@rowcount after a select statement
gives the total number of rows selected, but that is of no
use to my case. Mark's proposition is in teh sense of what I
would like to do.

> Or maybe you can use "select @@rowcount" right after your
> "select" statement directly
>
> Best Regards
> Eisen
> > You'll have to have a way of passing the last/next row
> > number between result set rows (eg, use an application
> > context function aka ACF), and you'll need to supply a
> > result set column/value as an argument to the function
> > to insure it fires for each line.
> >
> > With these requirements in mind, just off the top of my
> > head ...
> >
> > ======================
> > create function row_number
> > (@placeholder int) --change datatype based on input
> > column's type
> > returns smallint
> > as
> > declare @rn smallint, @dummy int
> > -- get last value and add 1
> > select
> > @rn=isnull(convert(smallint,get_appcontext('row'
> > ,'number')),0)+1 -- update ACF with new value
> > select @dummy=0 * rm_appcontext('row','number') +
> > set_appcontext('row','number',convert(varchar
> > ,@rn)) return @rn
> > ======================
> >
> > Before your query you'll need to clear the ACF, eg:
> >
> > ======================
> > declare @dummy int
> > select @dummy=rm_appcontext('row','number')
> >
> > select dbo.row_number(id),id,name
> > from sysobjects
> > -- order by name
> > ======================
> >
> >
> > > Has anyone got a function (ASE 15.03 and above) which
> > > can provide the row number in a select statement? I
> > > would appreciate to have copy of it.
> > > I know naturally that we can put the results of the
> > > select statement into a temporary table with an
> > > identity column and then select from the temporary
> > > table ; but I am seeking for a way to avoid the double
> > > select from the query to temp table and then from the
> > temp table.