Can somebody in Earth run this statement?

   ALTER TABLE "maTABIVA"
     ALTER Codigo AS SMALLINT DEFAULT 0 NOT NULL CHECK (Codigo
BETWEEN 0 AND 9999) DESCRIPTION 'Código' AT 01,
     ALTER Nombre AS VARCHAR(20) NOT NULL CHECK (Nombre > '') AT 02,
     ALTER TipoIVA AS FLOAT  DEFAULT 0 NOT NULL DESCRIPTION 'Tipo' AT
03,
     ALTER TipoRecargo AS TipoReq FLOAT DEFAULT 0 NOT NULL
DESCRIPTION 'Recargo Eq.' AT 04;

Two facts are there:
1. AT <ColumPos> not working
2. Renaming a field ( ALTER TipoRecargo AS TipoReq FLOAT ) not working

My be I'm stupid or documentation is wrong ...

I've some dozens of tables coming from an old app in Paradox. After
migrating them, I just want to add constraints, rename some fields and
reorder them. In DBISAM I made this a lot with no surprises, but here,
I becoming crazy

Any help?


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago


At first glance it all looks ok. Try doing it in EDBManager, if it works in there look at the sql history and clone the code, if not it might give you a reason.

Roy Lambert [Team Elevate]

Roy,

>At first glance it all looks ok. Try doing it in EDBManager, if it works in there look at the sql history and clone the code, if not it might give you a reason.

Try to rename a column in EDBManager and ... you get this exception:
"You cannot alter the name of an existing column". So there is no
chance to rename a column nor GUI or SQL code.

Respect to reorder columns combined with other alteratins, SQL history
shows:

ALTER TABLE "maTABIVA"
ALTER COLUMN "SubCuIVASop" MOVE TO 2,
ALTER COLUMN "SubCuIVASop" MOVE TO 4,
ALTER COLUMN "SubCuIVASop" MOVE TO 5,
ALTER COLUMN "SubCuIVASop" MOVE TO 6,
ALTER COLUMN "SubCuIVASop" MOVE TO 7,
ALTER COLUMN "SubCuIVARep" MOVE TO 5,
ALTER COLUMN "SubCuIVARep" MOVE TO 6,
ALTER COLUMN "SubCuIVARep" AS VARCHAR(20) COLLATE "ANSI",
ALTER COLUMN "SubCuIVASop" AS VARCHAR(20) COLLATE "ANSI",
ALTER COLUMN "Nombre" AS VARCHAR(20) COLLATE "ESP_CI_AI" NOT NULL

(See: every column move was recorded!)

I guess in EDB you cannot alter all in a column in a single sentence
(despite documentantion shows it), you need to duplicate lines for
every modification. Similar to alter table description, you must
provide two alter table sentences.

IMHO multiple alter column lines is right, but cannot rename a column
is a bug or bad documented.


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago


Sorry, I should have "glanced" deeper. At its current state of development you can't rename a column (think of index, RI, Trigger, SP implications) so you have to create your new column, move the data across and drop the old column. Hopefully at some point Tim will have enough free time to be able to put the effort into sorting this one out.

Roy Lambert [Team Elevate]

Tiago


I should also have said that you don't need all the MOVEs just the last one in the sequence for each column.

Roy Lambert [Team Elevate]

Roy,

>At its current state of development you can't rename a column (think of index, RI, Trigger, SP implications) so you have to create your new column, move the data across and drop the old column. Hopefully at some point Tim will have enough free time to be able to put the effort into sorting this one out.

This is I used to do in the remote times of Paradox before the DBISAM
era ...

Would be great docs have a note "not implemented yet" ...


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago,

<< This is I used to do in the remote times of Paradox before the DBISAM era
.... >>

It's also not part of the SQL 2003 standard, which is why it wasn't in EDB
initially and still isn't in there.

<< Would be great docs have a note "not implemented yet" ... >>

We don't indicate anywhere that it is supported.  The syntax diagram for the
ALTER TABLE statement clearly shows that ALTER COLUMN only allows for
redefining the column type and information, not the name.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tiago,

<< (See: every column move was recorded!) >>

If you use drag and drop in 2.02 and higher, you won't see all of the
individual moves.  The EDB Manager has to record every move when you move
each column individually, since it has to recreate the alterations exactly
as you have executed them in the Alter Table dialog.

<< I guess in EDB you cannot alter all in a column in a single sentence
(despite documentantion shows it), you need to duplicate lines for every
modification. >>

Absolutely not true.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>It's also not part of the SQL 2003 standard, which is why it wasn't in EDB
>initially and still isn't in there.
Ok, but you agree with me things were easier with DBISAM

>We don't indicate anywhere that it is supported.  The syntax diagram for the
>ALTER TABLE statement clearly shows that ALTER COLUMN only allows for
>redefining the column type and information, not the name.

Syntax
ALTER TABLE <Name>
....
[ALTER [COLUMN] <ColumnName> <ColumnAlterOptions>|AS
<ColumnDefinition>

I see here:

ALTER <ColumnName> AS <ColumnDefinition>

where <ColumnDefinition> is  "<Name> <DataType> ... "

then,  "ALTER <ColumnName> AS <Name> <DataType>" looks right.



Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tim

><< I guess in EDB you cannot alter all in a column in a single sentence
>(despite documentantion shows it), you need to duplicate lines for every
>modification. >>
>
>Absolutely not true.

Receive my apologies for my wilfulness, but how to reorder a column
and add not null, default, check, description in just one sentence?

ALTER TABLE "maTABIVA"
     ALTER Codigo AS SMALLINT DEFAULT 0 NOT NULL CHECK (Codigo
BETWEEN 0 AND 9999) DESCRIPTION 'Código' AT 01;

This code does not work, and the form  

ALTER TABLE "maTABIVA"
     ALTER Codigo MOVE TO 1 AS SMALLINT DEFAULT 0 NOT NULL CHECK
(Codigo BETWEEN 0 AND 9999) DESCRIPTION 'Código';

also fails


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.