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.

Identity Function in Sybase ASE ?

5 posts in General Discussion Last posting was on 2013-02-01 01:56:04.0Z
Anthony Posted on 2013-01-17 18:42:11.0Z
Sender: 5e79.50f845b5.1804289383@sybase.com
From: Anthony
Newsgroups: sybase.public.ase.general
Subject: Identity Function in Sybase ASE ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50f84603.5e85.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Jan 2013 10:42:11 -0800
X-Trace: forums-1-dub 1358448131 172.20.134.41 (17 Jan 2013 10:42:11 -0800)
X-Original-Trace: 17 Jan 2013 10:42:11 -0800, 172.20.134.41
Lines: 4
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31660
Article PK: 1159101

I know that in SQL Server 2005 there is an 'Identity'
function.

Is there an equivalent function in Sybase ASE ?


Rob V Posted on 2013-01-17 21:34:31.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Identity Function in Sybase ASE ?
References: <50f84603.5e85.1681692777@sybase.com>
In-Reply-To: <50f84603.5e85.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: <50f86e67$1@forums-1-dub>
Date: 17 Jan 2013 13:34:31 -0800
X-Trace: forums-1-dub 1358458471 172.20.134.152 (17 Jan 2013 13:34:31 -0800)
X-Original-Trace: 17 Jan 2013 13:34:31 -0800, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31661
Article PK: 1159105


On 17-Jan-2013 19:42, Anthony wrote:
> I know that in SQL Server 2005 there is an 'Identity'
> function.
>
> Is there an equivalent function in Sybase ASE ?
>

Yes, ASE supports identity columns. There are also various built-in
functions related to identity columns in ASE.
Exactly what are you looking for?

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Anthony Posted on 2013-01-31 14:52:21.0Z
Sender: 689f.510a82e1.1804289383@sybase.com
From: Anthony
Newsgroups: sybase.public.ase.general
Subject: Re: Identity Function in Sybase ASE ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510a8525.69b9.1681692777@sybase.com>
References: <50f86e67$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Jan 2013 06:52:21 -0800
X-Trace: forums-1-dub 1359643941 172.20.134.41 (31 Jan 2013 06:52:21 -0800)
X-Original-Trace: 31 Jan 2013 06:52:21 -0800, 172.20.134.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31687
Article PK: 1307402


> On 17-Jan-2013 19:42, Anthony wrote:
> > I know that in SQL Server 2005 there is an 'Identity'
> > function.
> >
> > Is there an equivalent function in Sybase ASE ?
> >
>
> Yes, ASE supports identity columns. There are also various
> built-in functions related to identity columns in ASE.
> Exactly what are you looking for?
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

I am trying to do the following in Sybase ASE, but keep
getting execution errors in the stored procedure that this
code is in.
(This code does work in a SQL Server 2005 stored procedure)

SELECT RowNumber = IDENTITY(9),
t.he_id,
t.ur_id,
t.or_start_dt,
t.he_stts_typ_cd,
t.ss_eff_dt
INTO tmp_i_in_ow_hi_da
FROM
(SELECT DISTINCT
a.he_id,
b.ur_id,
b.or_start_dt,
a.he_stts_typ_cd,
a.ss_eff_dt
FROM i_in_ow b JOIN i_he a ON (a.he_id = b.he_id)
) t
ORDER BY t.he_id, t.ow_start_dt


Bret Halford Posted on 2013-01-31 16:08:15.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Identity Function in Sybase ASE ?
References: <50f86e67$1@forums-1-dub> <510a8525.69b9.1681692777@sybase.com>
In-Reply-To: <510a8525.69b9.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: <510a96ef$1@forums-1-dub>
Date: 31 Jan 2013 08:08:15 -0800
X-Trace: forums-1-dub 1359648495 172.20.134.152 (31 Jan 2013 08:08:15 -0800)
X-Original-Trace: 31 Jan 2013 08:08:15 -0800, vip152.sybase.com
Lines: 114
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31688
Article PK: 1307405


On 1/31/2013 7:52 AM, Anthony wrote:
>> On 17-Jan-2013 19:42, Anthony wrote:
>>> I know that in SQL Server 2005 there is an 'Identity'
>>> function.
>>>
>>> Is there an equivalent function in Sybase ASE ?
>>>
>>
>> Yes, ASE supports identity columns. There are also various
>> built-in functions related to identity columns in ASE.
>> Exactly what are you looking for?
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>
> I am trying to do the following in Sybase ASE, but keep
> getting execution errors in the stored procedure that this
> code is in.
> (This code does work in a SQL Server 2005 stored procedure)
>
> SELECT RowNumber = IDENTITY(9),
> t.he_id,
> t.ur_id,
> t.or_start_dt,
> t.he_stts_typ_cd,
> t.ss_eff_dt
> INTO tmp_i_in_ow_hi_da
> FROM
> (SELECT DISTINCT
> a.he_id,
> b.ur_id,
> b.or_start_dt,
> a.he_stts_typ_cd,
> a.ss_eff_dt
> FROM i_in_ow b JOIN i_he a ON (a.he_id = b.he_id)
> ) t
> ORDER BY t.he_id, t.ow_start_dt
>

What are the exact errors you are getting?

It looks to me like you should be getting an error about the
"t.ow_start_dt" field in the ORDER BY not being a column
in the derived table 't'.

With that issue resolved by changing the name, identity(n) seems
to work fine for me...

1> select @@version
2> go


---------------------------------------------------------------------------------
Adaptive Server Enterprise/15.7.0/EBF 20804 SMP ESD#04 /P/Sun_svr4/OS
5.10/ase157x/3246/64-bit/FBO/Sat Jan 5 09:32:34 2013

use tempdb
go
create table i_in_ow (ur_id int, he_id int, or_start_dt datetime)
go
create table i_he (he_id int)
go
insert i_in_ow values (1,1,'1/1/1900')
insert i_in_ow values (2,1,'1/1/1900')
insert i_he values (1)
insert i_he values (2)
go

SELECT RowNumber = IDENTITY(9),
t.he_id
INTO tmp_i_in_ow_hi_da
FROM
(SELECT DISTINCT
a.he_id,
b.ur_id,
b.or_start_dt
FROM i_in_ow b JOIN i_he a ON (a.he_id = b.he_id)
) t
ORDER BY t.he_id, t.or_start_dt
go
select * from tmp_i_in_ow_hi_da
go

RowNumber he_id
------------ -----------
1 1
2 1

(2 rows affected)
--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


hy Posted on 2013-02-01 01:56:04.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <50f86e67$1@forums-1-dub> <510a8525.69b9.1681692777@sybase.com> <510a96ef$1@forums-1-dub>
In-Reply-To: <510a96ef$1@forums-1-dub>
Subject: Re: Identity Function in Sybase ASE ?
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=response
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <510b20b4@forums-1-dub>
Date: 31 Jan 2013 17:56:04 -0800
X-Trace: forums-1-dub 1359683764 172.20.134.152 (31 Jan 2013 17:56:04 -0800)
X-Original-Trace: 31 Jan 2013 17:56:04 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31689
Article PK: 1307419

And 2 points that Antohny should check:

1. Have you check "select into/bulkcopy" in database option ?

2. Old version of ASE does not support derived table. As I remember, it is 12.0/12.5 or before ...


HTH


"Bret Halford" <bret.halford@sap.com> ??????????????????????????? 510a96ef$1@forums-1-dub ?????????...

> On 1/31/2013 7:52 AM, Anthony wrote:
>>> On 17-Jan-2013 19:42, Anthony wrote:
>>>> I know that in SQL Server 2005 there is an 'Identity'
>>>> function.
>>>>
>>>> Is there an equivalent function in Sybase ASE ?
>>>>
>>>
>>> Yes, ASE supports identity columns. There are also various
>>> built-in functions related to identity columns in ASE.
>>> Exactly what are you looking for?
>>>
>>> --
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Professional DBA for Sybase ASE, IQ, Replication
>>> Server
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>>> "The Complete Sybase ASE Quick Reference Guide"
>>> "The Complete Sybase Replication Server Quick Reference
>>> Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>> Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>
>> I am trying to do the following in Sybase ASE, but keep
>> getting execution errors in the stored procedure that this
>> code is in.
>> (This code does work in a SQL Server 2005 stored procedure)
>>
>> SELECT RowNumber = IDENTITY(9),
>> t.he_id,
>> t.ur_id,
>> t.or_start_dt,
>> t.he_stts_typ_cd,
>> t.ss_eff_dt
>> INTO tmp_i_in_ow_hi_da
>> FROM
>> (SELECT DISTINCT
>> a.he_id,
>> b.ur_id,
>> b.or_start_dt,
>> a.he_stts_typ_cd,
>> a.ss_eff_dt
>> FROM i_in_ow b JOIN i_he a ON (a.he_id = b.he_id)
>> ) t
>> ORDER BY t.he_id, t.ow_start_dt
>>
>
> What are the exact errors you are getting?
>
> It looks to me like you should be getting an error about the "t.ow_start_dt" field in the ORDER BY not being a column
> in the derived table 't'.
>
> With that issue resolved by changing the name, identity(n) seems
> to work fine for me...
>
> 1> select @@version
> 2> go
>
>
> ---------------------------------------------------------------------------------
> Adaptive Server Enterprise/15.7.0/EBF 20804 SMP ESD#04 /P/Sun_svr4/OS 5.10/ase157x/3246/64-bit/FBO/Sat Jan 5 09:32:34 2013
>
> use tempdb
> go
> create table i_in_ow (ur_id int, he_id int, or_start_dt datetime)
> go
> create table i_he (he_id int)
> go
> insert i_in_ow values (1,1,'1/1/1900')
> insert i_in_ow values (2,1,'1/1/1900')
> insert i_he values (1)
> insert i_he values (2)
> go
>
> SELECT RowNumber = IDENTITY(9),
> t.he_id
> INTO tmp_i_in_ow_hi_da
> FROM
> (SELECT DISTINCT
> a.he_id,
> b.ur_id,
> b.or_start_dt
> FROM i_in_ow b JOIN i_he a ON (a.he_id = b.he_id)
> ) t
> ORDER BY t.he_id, t.or_start_dt
> go
> select * from tmp_i_in_ow_hi_da
> go
>
> RowNumber he_id
> ------------ -----------
> 1 1
> 2 1
>
> (2 rows affected)
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary Support
> Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021