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.

alter table with default function

3 posts in General Discussion Last posting was on 2012-09-14 09:32:56.0Z
rick_806 Posted on 2012-09-13 18:40:28.0Z
Sender: 16a4.50522745.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.general
Subject: alter table with default function
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5052289c.1751.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Sep 2012 11:40:28 -0700
X-Trace: forums-1-dub 1347561628 172.20.134.41 (13 Sep 2012 11:40:28 -0700)
X-Original-Trace: 13 Sep 2012 11:40:28 -0700, 172.20.134.41
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31380
Article PK: 74268

ASE 15.5 According to the Sybase docs I can add a new column
to a table and set a DEFAULT using a built-in function. It
does not work, at least not in any syntax I cam put
together? Is the documentation wrong or am I?

alter table test_rwm add col_c datetime DEFAULT getutcdate()
Error:
Default cannot be an expression

15.0 DOCs:
default
specifies a default value for a column. If you specify a
default and the user does not provide a value for this
column when inserting data, Adaptive Server inserts this
value. The default can be a constant_expression, user (to
insert the name of the user who is inserting the data), or
null (to insert the null value).

Adaptive Server generates a name for the default in the form
of tabname_colname_objid, where tabname is the first 10
characters of the table name, colname is the first 5
characters of the column name, and objid is the object ID
number for the default. Setting the default to null drops
the default.

constant_expression
is a constant expression to use as a default value for a
column. It cannot include global variables, the name of any
columns, or other database objects, but can include built-in
functions. This default value must be compatible with the
datatype of the column.



thx,
rick_806


Mark L Posted on 2012-09-13 19:48:12.0Z
From: Mark L <mlibner@yahoo.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: alter table with default function
References: <5052289c.1751.1681692777@sybase.com>
In-Reply-To: <5052289c.1751.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: <5052387c$1@forums-1-dub>
Date: 13 Sep 2012 12:48:12 -0700
X-Trace: forums-1-dub 1347565692 172.20.134.152 (13 Sep 2012 12:48:12 -0700)
X-Original-Trace: 13 Sep 2012 12:48:12 -0700, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31381
Article PK: 74269

Hi Rick,

The only difference between your syntax and mine is the inclusion of
allowing nulls and a create instead of alter.

Here's an example that works for me.

create table dbo.t_sec_sys (
sys_id uniqueidentifier not null default newid(),
sys_name varchar(80) null,
deleted_yn char(1) null default 'N',
entry_userid varchar(30) null default suser_name(),
entry_datetime datetime null default getutcdate(),
last_chg_userid varchar(30) null default suser_name(),
last_chg_datetime datetime null default getutcdate(),
timestamp timestamp null
)

hth,
Mark

On 9/13/2012 2:40 PM, rick_806 wrote:
> ASE 15.5 According to the Sybase docs I can add a new column
> to a table and set a DEFAULT using a built-in function. It
> does not work, at least not in any syntax I cam put
> together? Is the documentation wrong or am I?
>
> alter table test_rwm add col_c datetime DEFAULT getutcdate()
> Error:
> Default cannot be an expression
>
> 15.0 DOCs:
> default
> specifies a default value for a column. If you specify a
> default and the user does not provide a value for this
> column when inserting data, Adaptive Server inserts this
> value. The default can be a constant_expression, user (to
> insert the name of the user who is inserting the data), or
> null (to insert the null value).
>
> Adaptive Server generates a name for the default in the form
> of tabname_colname_objid, where tabname is the first 10
> characters of the table name, colname is the first 5
> characters of the column name, and objid is the object ID
> number for the default. Setting the default to null drops
> the default.
>
> constant_expression
> is a constant expression to use as a default value for a
> column. It cannot include global variables, the name of any
> columns, or other database objects, but can include built-in
> functions. This default value must be compatible with the
> datatype of the column.
>
>
>
> thx,
> rick_806
>


Rob V Posted on 2012-09-14 09:32:56.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: alter table with default function
References: <5052289c.1751.1681692777@sybase.com>
In-Reply-To: <5052289c.1751.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: <5052f9c8$1@forums-1-dub>
Date: 14 Sep 2012 02:32:56 -0700
X-Trace: forums-1-dub 1347615176 172.20.134.152 (14 Sep 2012 02:32:56 -0700)
X-Original-Trace: 14 Sep 2012 02:32:56 -0700, vip152.sybase.com
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31382
Article PK: 74270


On 13-Sep-2012 20:40, rick_806 wrote:
> ASE 15.5 According to the Sybase docs I can add a new column
> to a table and set a DEFAULT using a built-in function. It
> does not work, at least not in any syntax I cam put
> together? Is the documentation wrong or am I?
>
> alter table test_rwm add col_c datetime DEFAULT getutcdate()
> Error:
> Default cannot be an expression
>
> 15.0 DOCs:
> default
> specifies a default value for a column. If you specify a
> default and the user does not provide a value for this
> column when inserting data, Adaptive Server inserts this
> value. The default can be a constant_expression, user (to
> insert the name of the user who is inserting the data), or
> null (to insert the null value).
>
> Adaptive Server generates a name for the default in the form
> of tabname_colname_objid, where tabname is the first 10
> characters of the table name, colname is the first 5
> characters of the column name, and objid is the object ID
> number for the default. Setting the default to null drops
> the default.
>
> constant_expression
> is a constant expression to use as a default value for a
> column. It cannot include global variables, the name of any
> columns, or other database objects, but can include built-in
> functions. This default value must be compatible with the
> datatype of the column.
>
>
>
> thx,
> rick_806
>

The documentation is not wrong, but it is indeed a bit confusing.
The issue is that specifying a DEFAULT clause when adding a column, can
actually mean two things which the docs do not clearly split out:

- you add the column and immediately assign the value in the default
clause to each column, i.e.:

alter table mytable add mycol int default 999

This works only when the default expression is a single constant as
above (specifying 1+2 will give you the same error message).
At the same time, a default object is attached to this column, so for
any future inserts into the table where no value is specified for this
column, this default is used.

- you add the column and attach a default object to the column but do
not assign a value right away, but assign NULL instead. This requires
that the column's datatype is nullable, and it does allow you to specify
built-in functions and expressions. In order to use a nullable datatype
in 'alter table' you must create a user-defined datatype first since you
cannot specify the nullability directly in 'alter table':

sp_addtype mytype, "datetime", "null"

alter table mytable add mycol2 mytype default getutcdate()


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
-----------------------------------------------------------------