March 8, 2005 at 7:08 am
I have a column in my table (let's call it Col014), and I have some NULL values...along with whole values such as 197, 180 etc...
How can I write the CASE statment so that whenever Col014 ISNULL then change the value of Col014 to 7.45???
This is important because I need to sum up the values in this column and need to replace the Null values with 7.45 to make this work...
Can someone give me a helping hand?
Thank you!
March 8, 2005 at 8:06 am
HI
This is what u r looking for:
select isnull(Col014,7.45) from your_table
hth
JP
March 8, 2005 at 8:19 am
CASE
WHEN COL014 IS NULL THEN COL014 = 7.45
.
.
may do...
March 9, 2005 at 1:19 am
No, that will not do. You cannot assign the output value to the column in the CASE statement.
the statement : select ISNULL(COL014, 7.45) AS COL014 would work fine. Prefer the flexibility of the COALESCE function which allows you to examine several values successively for NULL.
HABIB.
March 9, 2005 at 4:36 am
Alternatively, if the data column must include a value why not update the column to 7.45 where you have NULLS. Will make the actual query easier!
March 9, 2005 at 7:10 am
UPDATE tblMyTable
SET Col014 = 7.45
WHERE Col014 IS NULL
March 9, 2005 at 10:59 am
SELECT Col014 = COALESCE(Col014 , 7.45 ) , * FROM tblMyTable
March 9, 2005 at 7:43 pm
Once you have all null values updated to 7.45 in the table, you may want to set Col014 to not null and set a default value of 7.45 on the field to prevent any more nulls.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply