Need help with complex column creation command

[former_member329524]

Hello, all

I need help with a complex column creation command and SQL anywhere help is not sufficient for it.

Here is the situation:

I need to write a generic DDL "alter table" command, which can add/modify columns without knowing in advance if they already exist in the destination table.

Is there a command, which looks like:

alter table "table1" add (on existing modify) column1 <datatype> <default> ?

Thank you,

Arcady

[Former Member · Accepted solution]

Hi.

I don't think this is supported in alter table command. But you can code that inside an if statement which queries systables & syscolumns. Your code should be something like that:

if (select count(*) from sysobjects, syscolumns where sysobjects.id = syscolumns.id and sysobjects.name = 'some_table' and syscolumns.name = 'some_column') < 1

begin

    alter table some_table add some_column numeric(12) not null

end

This is an example..

Andreas.

[former_member329524 · Accepted solution]

Thank you, Andreas

I was hoping for something simple, though.

If there, at least, an "if (not) exists" clause? Then, the exact same statement could be rewritten automatically, first with "add" and then with "modify"...

[Former Member · Accepted solution]

Ok.

What if you create your own function? That way you can simplify the example I wrote.

Let say we have created a function uf_add_missing_column which takes as params the table name and column name creates the column if not found?

It should be easy to modify those command to a single function.

Andreas.

[former_member188493 · Accepted solution]

Don't confuse "easy to use" with "easy to implement"; in fact, the exact opposite is often the case: it is often very difficult to implement a feature that is easy to use.

Andreas has given you some old-school Transact SQL code to implement exactly what you are asking for (an IF EXISTS). Alternative code using the modern catalog views SYSTAB and SYSTABCOL will be different, but not any simpler. You will not find any simpler equivalent such as "IF EXISTS COLUMN c IN TABLE t" built in to SQL Anywhere... you have to build it yourself. Andreas has suggested packaging that logic in a function which is a good idea.

Alternatively, you can as for a new feature; just post a new message here, and wait...