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.

Modify Column

5 posts in General Discussion Last posting was on 2005-06-23 14:40:45.0Z
Eddie Sizemore Posted on 2005-06-22 18:46:46.0Z
From: "Eddie Sizemore" <esizemoreno@spamqx.net>
Newsgroups: ianywhere.public.general
Subject: Modify Column
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Original-NNTP-Posting-Host: 12-203-198-23.client.insightbb.com
Message-ID: <42b9b213@forums-2-dub>
X-Original-Trace: 22 Jun 2005 11:46:43 -0700, 12-203-198-23.client.insightbb.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Jun 2005 11:46:44 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Jun 2005 11:46:46 -0700
X-Trace: forums-1-dub 1119466006 10.22.108.75 (22 Jun 2005 11:46:46 -0700)
X-Original-Trace: 22 Jun 2005 11:46:46 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4580
Article PK: 8387

If I have a table with the following def how do I change the FaxInvoice
Column to allow NULLS
ALTER TABLE "DBA"."Customer" ADD "FaxInvoice" INTEGER NOT NULL
CHECK(@COLUMN IN (0,1));

If I try the following it still show up in Sybase central as not allowing
nulls.
ALTER TABLE "DBA"."Customer" MODIFY "FaxInvoice" INTEGER NULL;

I can drop the check by setting it to null but it still will not allow nulls
on insert

Eddie Sizemore

ASA 9.xx


anil k goel Posted on 2005-06-23 12:59:26.0Z
From: "anil k goel" <firstname.no-junk.lastname@spam.ianywhere.com>
Newsgroups: ianywhere.public.general
References: <42b9b213@forums-2-dub>
Subject: Re: Modify Column
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-Original-NNTP-Posting-Host: iarouter.sybase.com
Message-ID: <42bab22c$1@forums-2-dub>
X-Original-Trace: 23 Jun 2005 05:59:24 -0700, iarouter.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Jun 2005 05:59:25 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Jun 2005 05:59:26 -0700
X-Trace: forums-1-dub 1119531566 10.22.108.75 (23 Jun 2005 05:59:26 -0700)
X-Original-Trace: 23 Jun 2005 05:59:26 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4583
Article PK: 8388

Please do use Breck's workaround for resetting the nullability for the
column.

I will investigate why your syntax is ignoring the NULL specification -- it
is being done deliberately, so I need to determine the reason behind it.

The difference between your syntax and Breck's, at least at a theoretical
level is that yours is specifying a change in datatype (and nullability)
while Breck's is only asking for a change in nullability while leaving the
datatype alone.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Eddie Sizemore" <esizemoreno@spamqx.net> wrote in message
news:42b9b213@forums-2-dub...
> If I have a table with the following def how do I change the FaxInvoice
> Column to allow NULLS
> ALTER TABLE "DBA"."Customer" ADD "FaxInvoice" INTEGER NOT NULL
> CHECK(@COLUMN IN (0,1));
>
> If I try the following it still show up in Sybase central as not allowing
> nulls.
> ALTER TABLE "DBA"."Customer" MODIFY "FaxInvoice" INTEGER NULL;
>
> I can drop the check by setting it to null but it still will not allow
> nulls
> on insert
>
> Eddie Sizemore
>
> ASA 9.xx
>


Breck Carter [TeamSybase] Posted on 2005-06-23 14:26:09.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Modify Column
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <l9hlb1hckro9tpgr117qtfnn8sdu0p5510@4ax.com>
References: <42b9b213@forums-2-dub> <42bab22c$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 12.24.172.2
X-Original-Trace: 23 Jun 2005 07:26:04 -0700, 12.24.172.2
Lines: 51
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 23 Jun 2005 07:26:07 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 23 Jun 2005 07:26:09 -0700
X-Trace: forums-1-dub 1119536769 10.22.108.75 (23 Jun 2005 07:26:09 -0700)
X-Original-Trace: 23 Jun 2005 07:26:09 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4586
Article PK: 8391

On 23 Jun 2005 05:59:26 -0700, "anil k goel"

<firstname.no-junk.lastname@spam.ianywhere.com> wrote:

>Please do use Breck's workaround for resetting the nullability for the
>column.
>
>I will investigate why your syntax is ignoring the NULL specification -- it
>is being done deliberately, so I need to determine the reason behind it.
>
>The difference between your syntax and Breck's, at least at a theoretical
>level is that yours is specifying a change in datatype (and nullability)
>while Breck's is only asking for a change in nullability while leaving the
>datatype alone.

I thought his version did not agree with the Help, but at second
reading it *should* work according to

ALTER TABLE [ owner.]table-name
{ add-clause | modify-clause | drop-clause | rename-clause }

modify-clause :
MODIFY column-definition

column-definition :
column-name data-type [ [ NOT ] NULL ] [ DEFAULT default-value ]
[ column-constraint ... ]

My version followed this branch which leaves out the data type:

modify-clause :
MODIFY column-name { DEFAULT default-value
| [ NOT ] NULL
| [ CONSTRAINT constraint-name ]
CHECK { NULL | ( new-condition ) } }

It's voodoo, IMO... one reason why you won't see many ALTER statements
in the book :)

Breck






--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


anil k goel Posted on 2005-06-23 14:40:45.0Z
From: "anil k goel" <firstname.no-junk.lastname@spam.ianywhere.com>
Newsgroups: ianywhere.public.general
References: <42b9b213@forums-2-dub> <42bab22c$1@forums-2-dub> <l9hlb1hckro9tpgr117qtfnn8sdu0p5510@4ax.com>
Subject: Re: Modify Column
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: iarouter.sybase.com
X-Original-NNTP-Posting-Host: iarouter.sybase.com
Message-ID: <42bac9ed$1@forums-1-dub>
Date: 23 Jun 2005 07:40:45 -0700
X-Trace: forums-1-dub 1119537645 10.25.106.45 (23 Jun 2005 07:40:45 -0700)
X-Original-Trace: 23 Jun 2005 07:40:45 -0700, iarouter.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4588
Article PK: 8393


"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:l9hlb1hckro9tpgr117qtfnn8sdu0p5510@4ax.com...
> I thought his version did not agree with the Help, but at second
> reading it *should* work according to

Yes; it should.

It appears to be a historical oversight (while it was done a certain way at
the dawn of the ASA era, we could have made it to work in some later
version) and we will add it to our book, err, list of things to do.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------


Breck Carter [TeamSybase] Posted on 2005-06-22 19:16:15.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Modify Column
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <mudjb1tuu3t6sch4l4h08fjiojfdmnqrkf@4ax.com>
References: <42b9b213@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 12.24.172.2
X-Original-NNTP-Posting-Host: 12.24.172.2
Date: 22 Jun 2005 12:16:15 -0700
X-Trace: forums-1-dub 1119467775 12.24.172.2 (22 Jun 2005 12:16:15 -0700)
X-Original-Trace: 22 Jun 2005 12:16:15 -0700, 12.24.172.2
Lines: 35
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4581
Article PK: 8389

Try this:

ALTER TABLE "DBA"."Customer" MODIFY "FaxInvoice" NULL;

The syntax for ALTER TABLE may be the most convoluted in all of SQL,
and it's not just ASA :)

Breck

On 22 Jun 2005 11:46:46 -0700, "Eddie Sizemore"

<esizemoreno@spamqx.net> wrote:

>If I have a table with the following def how do I change the FaxInvoice
>Column to allow NULLS
> ALTER TABLE "DBA"."Customer" ADD "FaxInvoice" INTEGER NOT NULL
>CHECK(@COLUMN IN (0,1));
>
>If I try the following it still show up in Sybase central as not allowing
>nulls.
> ALTER TABLE "DBA"."Customer" MODIFY "FaxInvoice" INTEGER NULL;
>
>I can drop the check by setting it to null but it still will not allow nulls
>on insert
>
>Eddie Sizemore
>
>ASA 9.xx
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com