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.

newid() function in select statements

10 posts in Product Futures Discussion Last posting was on 2003-04-17 07:36:31.0Z
Luc Van der Veurst Posted on 2003-02-26 14:12:24.0Z
Sender: Luc <lucv@xs1.xs4all.nl>
From: Luc Van der Veurst <lucv@nospam.be>
Subject: newid() function in select statements
User-Agent: tin/1.5.16-20030125 ("Bubbles") (UNIX) (FreeBSD/4.5-RELEASE-p19 (i386))
Message-ID: <Q9V0VEa3CHA.181@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 26 Feb 2003 09:12:24 -0500
Lines: 15
NNTP-Posting-Host: xs1.xs4all.nl 194.109.3.11
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:968
Article PK: 95209

The new newid() function in 12.5.0.3 doesn't behave as I expected.
I thought to use it in situations where you have to insert a set
of tuples and generate a key at the same time, something like :

insert a
select newid(), .....
from ....
where ....

Unfortunately, newid() seems to be called only once, so all rows
in the select have the same newid value :-(.

Luc.
____________________________________________________________________
Luc Van der Veurst ISUG
Academic Hospital, VUB, Brussels, Belgium http://www.isug.com


Stefan Karlsson Posted on 2003-02-26 23:46:43.0Z
From: "Stefan Karlsson" <Stefan.Karlsson@Sybase.justsaynotospam.com>
References: <Q9V0VEa3CHA.181@forums.sybase.com>
Subject: Re: newid() function in select statements
Date: Wed, 26 Feb 2003 15:46:43 -0800
Lines: 32
Organization: Sybase, Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <xSG3fQf3CHA.263@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 10.22.120.34
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:969
Article PK: 95208


"Luc Van der Veurst" <lucv@nospam.be> wrote in message
news:Q9V0VEa3CHA.181@forums.sybase.com...
> The new newid() function in 12.5.0.3 doesn't behave as I expected.
> I thought to use it in situations where you have to insert a set
> of tuples and generate a key at the same time, something like :
>
> insert a
> select newid(), .....
> from ....
> where ....
>
> Unfortunately, newid() seems to be called only once, so all rows
> in the select have the same newid value :-(.
>
> Luc.
> ____________________________________________________________________
> Luc Van der Veurst ISUG
> Academic Hospital, VUB, Brussels, Belgium http://www.isug.com
>

Try:

CREATE TABLE MyTable (
col1 CHAR(32) DEFAULT NEWID()
, ...
)

HTH,

/Stefan


Luc Van der Veurst Posted on 2003-02-27 17:36:12.0Z
Sender: Luc <lucv@xs1.xs4all.nl>
From: Luc Van der Veurst <lucv@nospam.be>
Subject: Re: newid() function in select statements
References: <Q9V0VEa3CHA.181@forums.sybase.com> <xSG3fQf3CHA.263@forums.sybase.com>
User-Agent: tin/1.5.16-20030125 ("Bubbles") (UNIX) (FreeBSD/4.5-RELEASE-p19 (i386))
Message-ID: <inr24ao3CHA.261@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 27 Feb 2003 12:36:12 -0500
Lines: 41
NNTP-Posting-Host: xs1.xs4all.nl 194.109.3.11
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:970
Article PK: 95210

Stefan, thanks for your reply.

Stefan Karlsson <Stefan.Karlsson@sybase.justsaynotospam.com> wrote:
>> The new newid() function in 12.5.0.3 doesn't behave as I expected.
>> I thought to use it in situations where you have to insert a set
>> of tuples and generate a key at the same time, something like :
>>
>> insert a
>> select newid(), .....
>> from ....
>> where ....
>>
>> Unfortunately, newid() seems to be called only once, so all rows
>> in the select have the same newid value :-(.
>
> Try:
>
> CREATE TABLE MyTable (
> col1 CHAR(32) DEFAULT NEWID()
> , ...
> )

No, that generates the same key also :

create table t (i int);
create table k (c CHAR(32) DEFAULT NEWID(), i int);

insert t values (0);
insert t values (0);
insert t values (0);
insert t values (0);

insert k(i) select * from t;

select * from k;

c i
-------------------------------- -----------
11ada0d4dd6f4551b53019f7d762f2f0 0
11ada0d4dd6f4551b53019f7d762f2f0 0
11ada0d4dd6f4551b53019f7d762f2f0 0
11ada0d4dd6f4551b53019f7d762f2f0 0

but :

select newid(i), i from t;
i
------------------------------------ -----------
b4ca254c9a5d4218bc4f80355672a377 0
b3f8d62696794eb289c0b75c460de38c 0
a0578a2a51b348cea052074542958f69 0
19eaac71d2ca47288b79da1aa59820ae 0

I understand the behavior but I don't think it's consistent,
it should be considered to be a bug.

Luc.


Sethu M Posted on 2003-03-01 23:46:59.0Z
From: "Sethu M" <sethu@sybase.com>
References: <Q9V0VEa3CHA.181@forums.sybase.com> <xSG3fQf3CHA.263@forums.sybase.com> <inr24ao3CHA.261@forums.sybase.com>
Subject: Re: newid() function in select statements
Date: Sat, 1 Mar 2003 15:46:59 -0800
Lines: 30
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2720.3000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <aCI6l#E4CHA.299@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:978
Article PK: 95219

Hi Luc,

The newid() function is behaving like all other builtin functions. All
builtins
are treated CONSTANT and thus generate value only once unless it is passed
a dynamic variable (like column id or declared variable).

For e.g, if you take rand() function the following will return the same
random
number for each row in sysobjects.

select rand(), id from sysobjects where id <= 10

However the following will return different values.

select rand(id), id from sysobjects where id <= 10

One of the main reason it is done this way is to have a consistent result
set when query
constructs like this is done

select rand() from sysobjects where rand() = somenumber

If others are all feel that this functionality is to be enhanced to provide
different values in
the 1st query example then I'll file a feature request.

regards,
Sethu


putnamr Posted on 2003-03-03 02:50:20.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 2 Mar 2003 21:50:20 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: newid() function in select statements
Message-ID: <4252736B12538069000F984B85256CDE.000349B985256CDD@webforums>
References: <Q9V0VEa3CHA.181@forums.sybase.com> <xSG3fQf3CHA.263@forums.sybase.com> <inr24ao3CHA.261@forums.sybase.com> <aCI6l#E4CHA.299@forums.sybase.com>
Lines: 28
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:982
Article PK: 95222

Hello,

This should work. Keep in mind that what you are actually specifying is a
seed for random numbers to grow from. Hence, once planted it will always
grow the same.

drop table cartesian_product
go
create table cartesian_product (cola int not null)
go
insert into cartesian_product values (0)
go

DECLARE @TIME_SEED_SKEW INT

SELECT @TIME_SEED_SKEW = DATEPART(MS, GETDATE()) * 1000000 +
DATEPART(SS, GETDATE()) * 100000 +
DATEPART(MM, GETDATE()) * 10000 +
DATEPART(HH, GETDATE()) * 1000 +
DATEPART(MM, GETDATE()) * 100 +
DATEPART(YY, GETDATE())

SELECT NEWID(@@cpu_busy/10 + cola + @TIME_SEED_SKEW),
*
FROM sysdatabases,
cartesian_product
go


Luc Posted on 2003-03-04 09:25:13.0Z
From: Luc <lucv@xs1.xs4all.nl>
Subject: Re: newid() function in select statements
References: <Q9V0VEa3CHA.181@forums.sybase.com> <xSG3fQf3CHA.263@forums.sybase.com> <inr24ao3CHA.261@forums.sybase.com> <aCI6l#E4CHA.299@forums.sybase.com> <4252736B12538069000F984B85256CDE.000349B985256CDD@webforums>
User-Agent: tin/1.5.16-20030125 ("Bubbles") (UNIX) (FreeBSD/4.5-RELEASE-p19 (i386))
Message-ID: <v9z$1$i4CHA.303@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 04 Mar 2003 04:25:13 -0500
Lines: 43
NNTP-Posting-Host: xs1.xs4all.nl 194.109.3.11
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:983
Article PK: 95227


putnamr@river.it.gvsu.edu wrote:
>
> This should work. Keep in mind that what you are actually specifying is a
> seed for random numbers to grow from. Hence, once planted it will always
> grow the same.
>
> drop table cartesian_product
> go
> create table cartesian_product (cola int not null)
> go
> insert into cartesian_product values (0)
> go
>
> DECLARE @TIME_SEED_SKEW INT
>
> SELECT @TIME_SEED_SKEW = DATEPART(MS, GETDATE()) * 1000000 +
> DATEPART(SS, GETDATE()) * 100000 +
> DATEPART(MM, GETDATE()) * 10000 +
> DATEPART(HH, GETDATE()) * 1000 +
> DATEPART(MM, GETDATE()) * 100 +
> DATEPART(YY, GETDATE())
>
> SELECT NEWID(@@cpu_busy/10 + cola + @TIME_SEED_SKEW),
> *
> FROM sysdatabases,
> cartesian_product
> go
>

It doesn't have to be this complicated. Newid only needs 0 or 1 as
parameter, 0 for output without dashes, 1 for output with dashes.

create table newid_codes (no_dashes int not null, dashes int not null)
go
insert into newid_codes values (0,1)
go
select newid(no_dashes), name from sysdatabases, newid_codes
go

Sethu, this could be an acceptable workarround for my original
question (allthough there is a performance decrease). So, if no one
else wants to see the behavior of newid changed, I know how to deal
with it.

Luc.
____________________________________________________________________
Luc Van der Veurst ISUG
Academic Hospital, VUB, Brussels, Belgium http://www.isug.com


putnamr Posted on 2003-03-04 14:21:41.0Z
From: putnamr@river.it.gvsu.edu
Date: Tue, 4 Mar 2003 09:21:41 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: newid() function in select statements
Message-ID: <C8D3295CF695ABA4004EE3CF85256CDF.00368E8785256CDF@webforums>
References: <Q9V0VEa3CHA.181@forums.sybase.com> <xSG3fQf3CHA.263@forums.sybase.com> <inr24ao3CHA.261@forums.sybase.com> <aCI6l#E4CHA.299@forums.sybase.com> <4252736B12538069000F984B85256CDE.000349B985256CDD@webforums> <v9z$1$i4CHA.303@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:986
Article PK: 95225

Hello,

I guess I am not that familiar with newid() I assumed it took a seed like
rand(), appears that it doesn't. Thanks for the input, I am sure this will
come in handy in the future.

Thanks.


Rob Verschoor Posted on 2003-03-07 12:34:49.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <Q9V0VEa3CHA.181@forums.sybase.com>
Subject: Re: newid() function in select statements
Date: Fri, 7 Mar 2003 13:34:49 +0100
Lines: 43
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <kC6X5lK5CHA.135@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 158.76.4.40
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:988
Article PK: 95231

Actually, distinct newid() values *can* be generated for a multiple-row
result set. I am currently working on a "Tips&Tricks"-type ASE book which
(among others) will describe how to do this. I don't want to spill the
beans right now, so please bear with me... The book will hopefully be
published around May/June 2003.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Luc Van der Veurst" <lucv@nospam.be> wrote in message
news:Q9V0VEa3CHA.181@forums.sybase.com...
> The new newid() function in 12.5.0.3 doesn't behave as I expected.
> I thought to use it in situations where you have to insert a set
> of tuples and generate a key at the same time, something like :
>
> insert a
> select newid(), .....
> from ....
> where ....
>
> Unfortunately, newid() seems to be called only once, so all rows
> in the select have the same newid value :-(.
>
> Luc.
> ____________________________________________________________________
> Luc Van der Veurst ISUG
> Academic Hospital, VUB, Brussels, Belgium http://www.isug.com
>


Sherlock, Kevin Posted on 2003-04-16 22:15:20.0Z
Message-ID: <3E9DD5E1.6E3AB909@qwest.com.nospam>
Date: Wed, 16 Apr 2003 17:15:20 -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: newid() function in select statements
References: <Q9V0VEa3CHA.181@forums.sybase.com> <kC6X5lK5CHA.135@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Lines: 24
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1141
Article PK: 95382

Not trying to play spoiler here. Apologies to Rob if this is along the
lines of his solution.

Consider:

1. That ANY datatype (with the exception of TEXT and IMAGE datatypes)
can be converted to a BINARY datatype
2. The argument to newid() takes either a zero, or non-zero value to
generate and id, AND, if that argument is sourced from the table, you
will get unique id's for each row result.
3. Every table must have at least one column.

declare @format int
select @format = 1 -- Or zero if you don't want the dashes

select newid(
case convert(binary(1),ANY_TABLE.ANY_COLUMN)
when 0x00 then @format
else @format
end
)
from ANY_TABLE
where ...
...

Where "ANY_TABLE" is the table from which you are getting mutiple rows.
ANY_COLUMN is any column of that table of any datatype (other than TEXT,
or IMAGE)

Try it.


Rob Verschoor Posted on 2003-04-17 07:36:31.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <Q9V0VEa3CHA.181@forums.sybase.com> <kC6X5lK5CHA.135@forums.sybase.com> <3E9DD5E1.6E3AB909@qwest.com.nospam>
Subject: Re: newid() function in select statements
Date: Thu, 17 Apr 2003 09:36:31 +0200
Lines: 45
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <uaJGPULBDHA.330@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1143
Article PK: 95381

Not bad -- but the solution is in fact a lot simpler. Just keeping up
expectations ;-)
(I'll mail the details to Kevin)

Rob V.

"Sherlock, Kevin" <ksherlo@qwest.com.nospam> wrote in message
news:3E9DD5E1.6E3AB909@qwest.com.nospam...
> Not trying to play spoiler here. Apologies to Rob if this is along
the
> lines of his solution.
>
> Consider:
>
> 1. That ANY datatype (with the exception of TEXT and IMAGE
datatypes)
> can be converted to a BINARY datatype
> 2. The argument to newid() takes either a zero, or non-zero value
to
> generate and id, AND, if that argument is sourced from the table,
you
> will get unique id's for each row result.
> 3. Every table must have at least one column.
>
> declare @format int
> select @format = 1 -- Or zero if you don't want the dashes
>
> select newid(
> case convert(binary(1),ANY_TABLE.ANY_COLUMN)
> when 0x00 then @format
> else @format
> end
> )
> from ANY_TABLE
> where ...
> ...
>
> Where "ANY_TABLE" is the table from which you are getting mutiple
rows.
> ANY_COLUMN is any column of that table of any datatype (other than
TEXT,
> or IMAGE)
>
> Try it.