add column if it does not exist

[a***@gmail.com]

If a column does not already exist on a table I want to add it. The
column already exists in the database I am testing this in.

Here is my problem:

IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
END

results in:
Error: com.sybase.jdbc3.jdbc.SybSQLException: Column names in each
table must be unique. Column name 'Status' in table 'Person' is
specified more than once.
, SQL State: ZZZZZ, Error Code: 2705

However
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1) NULL
END
results in:
Query 1 of 1 elapsed time (seconds) - Total: 0, SQL query: 0, Building
output: 0 (success)

[Luc Van der Veurst]

Post by a***@gmail.com
If a column does not already exist on a table I want to add it. The
column already exists in the database I am testing this in.
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
END
Error: com.sybase.jdbc3.jdbc.SybSQLException: Column names in each
table must be unique. Column name 'Status' in table 'Person' is
specified more than once.
, SQL State: ZZZZZ, Error Code: 2705
However
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1) NULL
END
Query 1 of 1 elapsed time (seconds) - Total: 0, SQL query: 0, Building
output: 0 (success)

I tried your first query in an ASE 12.5.3 ES#5 server
(from within sqsh) and it didn't give any error.

What is your ASE's version ?

Luc.

[b***@sybase.com]

Post by a***@gmail.com
If a column does not already exist on a table I want to add it. The
column already exists in the database I am testing this in.
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
END
Error: com.sybase.jdbc3.jdbc.SybSQLException: Column names in each
table must be unique. Column name 'Status' in table 'Person' is
specified more than once.
, SQL State: ZZZZZ, Error Code: 2705
However
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1) NULL
END
Query 1 of 1 elapsed time (seconds) - Total: 0, SQL query: 0, Building
output: 0 (success)

Your solution would be to use dynamic SQL for the DDL. The error is
being raised during normalizations (as the parse tree is being
converted into a normalized query tree) rather than at execution. The
contents of dynamic sql aren't processed until they are actually
called, avoiding the error.

IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
EXECUTE (" ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
")
END

[a***@gmail.com]

Post by b***@sybase.com

Post by a***@gmail.com
If a column does not already exist on a table I want to add it. The
column already exists in the database I am testing this in.
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
END
Error: com.sybase.jdbc3.jdbc.SybSQLException: Column names in each
table must be unique. Column name 'Status' in table 'Person' is
specified more than once.
, SQL State: ZZZZZ, Error Code: 2705
However
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1) NULL
END
Query 1 of 1 elapsed time (seconds) - Total: 0, SQL query: 0, Building
output: 0 (success)

Your solution would be to use dynamic SQL for the DDL. The error is
being raised during normalizations (as the parse tree is being
converted into a normalized query tree) rather than at execution. The
contents of dynamic sql aren't processed until they are actually
called, avoiding the error.
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
EXECUTE (" ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
")
END

That worked. Thank you both for your responses.