Change datatype in computed Column in SQL

al4629740
al4629740 used Ask the Experts™
on
I have a computed column in SQL.  I would like to change the datatype to Decimal(18,2) if possible, because I need to enter partial numbers in the records.  How do I change the datatype for a computed column in SQL Express 2005?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
suppose this is your computed column.

col3 as col1 + co2

then this would suffice

col3 as cast(col1 + col2 as decimal(18,2))

Make sure columns col1 and col2 contain decimal values.
Not sure about SQL Server Express 2005... but...
1. If you're creating a new table, I would have thought you could select the datatype for each field from a drop-down list in design view.
2. If you want to change the datatype of an existing field, try the ALTER COLUMN statement
why don't you do something like this?

select col3=cast(col1 as decimal(18,2))+cast(col2 as decimal(18,2)) from tableName
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

if you want to add computed column in your table than do something like this:

ALTER TABLE tableName Add col3 AS (Col1+Col2)

if col1 and col2 is not decimal than do something like this.

ALTER TABLE tableName Add col3 AS (cast(Col1 as decimal(18,2))+cast(Col2 as decimal(18,2)))


Author

Commented:
But I want the Computed Column to SHOW a decimal value.  (ie 2.50)
If you use any of the above solution, it will show like 2.50, what values are you calculating?

Author

Commented:
I have decima(18,2) for each column.  

When I put 1.25, 1.5 and .1  in the columns, then the Total shows a NULL value
have a look at below script, it doesn't show you null:

declare @a decimal(18,2)
declare @b decimal(18,2)
declare @c decimal(18,2)

set @a = 1.5
set @b = 1.25
set @C = 1

select Col3=@a+@b+@c

Author

Commented:
Some columns work well and then other columns won't.  Why is that?
to get rid of null:

declare @a decimal(18,2)
declare @b decimal(18,2)
declare @c decimal(18,2)

set @a = 1.5
set @b = 1.25
set @C = NULL

--if you have any null value than it would happen,
--see below output
select Col3=@a+@b+@c

--you can get rid of NULL like this
select Col3=isnull(@a,0)+isnull(@b,0)+isnull(@c,0)

Author

Commented:
Sorry I meant some ROWS add fine and others don't.  Why?
>>Sorry I meant some ROWS add fine and others don't.  Why?<<

have you used my last solution with ISNULL function?

Author

Commented:
I am confused.  How do I make SQL remove the NULLs.  Can I put something in default?
you don't need to replace null. if you see my last post, I have runtime decided, if value is null than treat it with 0.

like this

select col3=isnull(col1,0)+isnull(col2,0) from tableName

so if value will be null, it will be treated with 0 otherwise it will take it's original value.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
al4629740,
   Kindly post Datatypes and Nullability for the columns involved so that we can help you create / alter the computed columns correctly.

This would help avoid these kind of confusions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial