Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
Chris
Hi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Convert decimals and money to fixed length varchar format.

I am trying to convert decimals and money data types to a universal format and convert to varchar. The format is:

+00.000000

The format must be exactly this format.
The sign must always be present.
There must always be two numbers to the left of the decimal.
There must always be six to the right.
No matter the value, round to six decimals.

Examples:

1.123 : +01.123000
-4 : -04.000000
.98672385 : +00.986724

I have tried every combo of Convert, Cast, Right, etc and every time it doesn't seem to work.

Any ideas?

It is recommended to do the formating in the presentation layer. Try:

select

case when sign(c1) < 0 then '-' else '+' end +

right('00' + str(abs(c1), 8, 6), 9)

from

(

select 1.123 as c1

union all

select -4

union all

select .98672385

) as t

AMB

|||Try this

Code Snippet

declare @.num decimal(18,6)

SET @.num = .98672385

SELECT FmtNum = CASE WHEN @.num < 0 THEN '-' ELSE '+' END +
RIGHT('00'+CONVERT(varchar(9),CONVERT(decimal(9,6),ABS(@.num))),9)

|||You rock.

Thanks. It works great.