At 11:41 p.m. 22/04/2015, Vishal Tiwari vishualsoft@... [firebird-support] wrote:

>If I make all values to null of that column which is of type varchar and then go for Alter, would you share the sql for it ?

Changing the values to null does not change the data type of the column. There is no "quick and dirty" way to do this change.

First, test the old column to see whether there are any data that would fail a conversion to integer:

select cast (bar as integer) test_me from foo;

If this works, the next thing to do is to attempt to rename the old column:

commit;
alter table foo alter column bar TO drop_me;

If there are dependencies on column bar, you will get errors. You can't proceed until you have resolved them.

If no errors,

commit;

alter table foo add bar integer;
commit;
update foo set bar = cast (drop_me as integer)
where drop_me is not null;
commit;

Test the data in the new column:

select (bar + 1) from foo;

If no errors, then

commit;
alter table foo drop column drop_me;
commit;

Last, if you want to, you can change the left-to-right position (degree) of the new column so that it is the same as the old column:

alter table foo alter bar position 99;
commit;


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________