Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
At 11:41 p.m. 22/04/2015, Vishal Tiwari vishuals...@yahoo.co.in [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 __
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
On Wed, 22 Apr 2015 05:38:39 + (UTC), Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com wrote: Hi All, I have a Table called Table1, in that I have a column called Status which is of type Varchar and contains values link '1', '2', '3' etc, i.e. all integer values only but in Varchar type. I need to change this column's datatype to Integer for some reason, any idea how do we do it ? I am trying like: ALTER TABLE Table1 ALTER STATUS TYPE Integer; But I am getting an error like, Cannot change datatype... Create a new column, assign/convert the values from the old column, drop the old column, rename the new column (optional). Mark
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
On 22/04/2015 09:26, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: Create a new column, assign/convert the values from the old column, drop the old column, rename the new column (optional). ... and sort out, manually, however many hundreds of dependencies you have (triggers, foreign keys, procedures, ...). -- Tim Ward
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
On Wed, 22 Apr 2015 09:28:09 +0100, Tim Ward t...@telensa.com [firebird-support] firebird-support@yahoogroups.com wrote: On 22/04/2015 09:26, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: Create a new column, assign/convert the values from the old column, drop the old column, rename the new column (optional). ... and sort out, manually, however many hundreds of dependencies you have (triggers, foreign keys, procedures, ...). True. But in this case I assume the happy case ;) Mark
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
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 ?
On Wednesday, 22 April 2015 3:10 PM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] firebird-support@yahoogroups.com wrote:
On Wed, 22 Apr 2015 09:00:18 + (UTC), Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support]
firebird-support@yahoogroups.com wrote:
Is there no Alter like SQL available ?
There is, but it doesn't support conversion from (VAR)CHAR to INTEGER. The
conversion isn't done at ALTER time, but when the field is queried or
updated. This means that Firebird cannot ensure that current values are
convertible to INTEGER, and therefor the conversion is not allowed.
Mark
#yiv7283385367 #yiv7283385367 -- #yiv7283385367ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7283385367
#yiv7283385367ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7283385367
#yiv7283385367ygrp-mkp #yiv7283385367hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7283385367 #yiv7283385367ygrp-mkp #yiv7283385367ads
{margin-bottom:10px;}#yiv7283385367 #yiv7283385367ygrp-mkp .yiv7283385367ad
{padding:0 0;}#yiv7283385367 #yiv7283385367ygrp-mkp .yiv7283385367ad p
{margin:0;}#yiv7283385367 #yiv7283385367ygrp-mkp .yiv7283385367ad a
{color:#ff;text-decoration:none;}#yiv7283385367 #yiv7283385367ygrp-sponsor
#yiv7283385367ygrp-lc {font-family:Arial;}#yiv7283385367
#yiv7283385367ygrp-sponsor #yiv7283385367ygrp-lc #yiv7283385367hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7283385367
#yiv7283385367ygrp-sponsor #yiv7283385367ygrp-lc .yiv7283385367ad
{margin-bottom:10px;padding:0 0;}#yiv7283385367 #yiv7283385367actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7283385367
#yiv7283385367activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7283385367
#yiv7283385367activity span {font-weight:700;}#yiv7283385367
#yiv7283385367activity span:first-child
{text-transform:uppercase;}#yiv7283385367 #yiv7283385367activity span a
{color:#5085b6;text-decoration:none;}#yiv7283385367 #yiv7283385367activity span
span {color:#ff7900;}#yiv7283385367 #yiv7283385367activity span
.yiv7283385367underline {text-decoration:underline;}#yiv7283385367
.yiv7283385367attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7283385367 .yiv7283385367attach div a
{text-decoration:none;}#yiv7283385367 .yiv7283385367attach img
{border:none;padding-right:5px;}#yiv7283385367 .yiv7283385367attach label
{display:block;margin-bottom:5px;}#yiv7283385367 .yiv7283385367attach label a
{text-decoration:none;}#yiv7283385367 blockquote {margin:0 0 0
4px;}#yiv7283385367 .yiv7283385367bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7283385367
.yiv7283385367bold a {text-decoration:none;}#yiv7283385367 dd.yiv7283385367last
p a {font-family:Verdana;font-weight:700;}#yiv7283385367 dd.yiv7283385367last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7283385367
dd.yiv7283385367last p span.yiv7283385367yshortcuts
{margin-right:0;}#yiv7283385367 div.yiv7283385367attach-table div div a
{text-decoration:none;}#yiv7283385367 div.yiv7283385367attach-table
{width:400px;}#yiv7283385367 div.yiv7283385367file-title a, #yiv7283385367
div.yiv7283385367file-title a:active, #yiv7283385367
div.yiv7283385367file-title a:hover, #yiv7283385367 div.yiv7283385367file-title
a:visited {text-decoration:none;}#yiv7283385367 div.yiv7283385367photo-title a,
#yiv7283385367 div.yiv7283385367photo-title a:active, #yiv7283385367
div.yiv7283385367photo-title a:hover, #yiv7283385367
div.yiv7283385367photo-title a:visited {text-decoration:none;}#yiv7283385367
div#yiv7283385367ygrp-mlmsg #yiv7283385367ygrp-msg p a
span.yiv7283385367yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7283385367
.yiv7283385367green {color:#628c2a;}#yiv7283385367 .yiv7283385367MsoNormal
{margin:0 0 0 0;}#yiv7283385367 o {font-size:0;}#yiv7283385367
#yiv7283385367photos div {float:left;width:72px;}#yiv7283385367
#yiv7283385367photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv7283385367
#yiv7283385367photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7283385367
#yiv7283385367reco-category {font-size:77%;}#yiv7283385367
#yiv7283385367reco-desc {font-size:77%;}#yiv7283385367 .yiv7283385367replbq
{margin:4px;}#yiv7283385367 #yiv7283385367ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv7283385367 #yiv7283385367ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7283385367
#yiv7283385367ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7283385367
#yiv7283385367ygrp-mlmsg select, #yiv7283385367 input, #yiv7283385367 textarea
{font:99%
Re: [firebird-support] How To Change Column DataType From Varchar To Integer ?
Is there no Alter like SQL available ?
On Wednesday, 22 April 2015 2:01 PM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] firebird-support@yahoogroups.com wrote:
On Wed, 22 Apr 2015 09:28:09 +0100, Tim Ward t...@telensa.com
[firebird-support] firebird-support@yahoogroups.com wrote:
On 22/04/2015 09:26, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
Create a new column, assign/convert the values from the old column,
drop
the old column, rename the new column (optional).
... and sort out, manually, however many hundreds of dependencies you
have (triggers, foreign keys, procedures, ...).
True. But in this case I assume the happy case ;)
Mark
#yiv6984208228 #yiv6984208228 -- #yiv6984208228ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6984208228
#yiv6984208228ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6984208228
#yiv6984208228ygrp-mkp #yiv6984208228hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6984208228 #yiv6984208228ygrp-mkp #yiv6984208228ads
{margin-bottom:10px;}#yiv6984208228 #yiv6984208228ygrp-mkp .yiv6984208228ad
{padding:0 0;}#yiv6984208228 #yiv6984208228ygrp-mkp .yiv6984208228ad p
{margin:0;}#yiv6984208228 #yiv6984208228ygrp-mkp .yiv6984208228ad a
{color:#ff;text-decoration:none;}#yiv6984208228 #yiv6984208228ygrp-sponsor
#yiv6984208228ygrp-lc {font-family:Arial;}#yiv6984208228
#yiv6984208228ygrp-sponsor #yiv6984208228ygrp-lc #yiv6984208228hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6984208228
#yiv6984208228ygrp-sponsor #yiv6984208228ygrp-lc .yiv6984208228ad
{margin-bottom:10px;padding:0 0;}#yiv6984208228 #yiv6984208228actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6984208228
#yiv6984208228activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6984208228
#yiv6984208228activity span {font-weight:700;}#yiv6984208228
#yiv6984208228activity span:first-child
{text-transform:uppercase;}#yiv6984208228 #yiv6984208228activity span a
{color:#5085b6;text-decoration:none;}#yiv6984208228 #yiv6984208228activity span
span {color:#ff7900;}#yiv6984208228 #yiv6984208228activity span
.yiv6984208228underline {text-decoration:underline;}#yiv6984208228
.yiv6984208228attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6984208228 .yiv6984208228attach div a
{text-decoration:none;}#yiv6984208228 .yiv6984208228attach img
{border:none;padding-right:5px;}#yiv6984208228 .yiv6984208228attach label
{display:block;margin-bottom:5px;}#yiv6984208228 .yiv6984208228attach label a
{text-decoration:none;}#yiv6984208228 blockquote {margin:0 0 0
4px;}#yiv6984208228 .yiv6984208228bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6984208228
.yiv6984208228bold a {text-decoration:none;}#yiv6984208228 dd.yiv6984208228last
p a {font-family:Verdana;font-weight:700;}#yiv6984208228 dd.yiv6984208228last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6984208228
dd.yiv6984208228last p span.yiv6984208228yshortcuts
{margin-right:0;}#yiv6984208228 div.yiv6984208228attach-table div div a
{text-decoration:none;}#yiv6984208228 div.yiv6984208228attach-table
{width:400px;}#yiv6984208228 div.yiv6984208228file-title a, #yiv6984208228
div.yiv6984208228file-title a:active, #yiv6984208228
div.yiv6984208228file-title a:hover, #yiv6984208228 div.yiv6984208228file-title
a:visited {text-decoration:none;}#yiv6984208228 div.yiv6984208228photo-title a,
#yiv6984208228 div.yiv6984208228photo-title a:active, #yiv6984208228
div.yiv6984208228photo-title a:hover, #yiv6984208228
div.yiv6984208228photo-title a:visited {text-decoration:none;}#yiv6984208228
div#yiv6984208228ygrp-mlmsg #yiv6984208228ygrp-msg p a
span.yiv6984208228yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6984208228
.yiv6984208228green {color:#628c2a;}#yiv6984208228 .yiv6984208228MsoNormal
{margin:0 0 0 0;}#yiv6984208228 o {font-size:0;}#yiv6984208228
#yiv6984208228photos div {float:left;width:72px;}#yiv6984208228
#yiv6984208228photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv6984208228
#yiv6984208228photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6984208228
#yiv6984208228reco-category {font-size:77%;}#yiv6984208228
#yiv6984208228reco-desc {font-size:77%;}#yiv6984208228 .yiv6984208228replbq
{margin:4px;}#yiv6984208228 #yiv6984208228ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv6984208228 #yiv6984208228ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6984208228
#yiv6984208228ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6984208228
#yiv6984208228ygrp-mlmsg select, #yiv6984208228 input, #yiv6984208228 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv6984208228
