July 12, 2002 at 12:07 pm
Hi everybody!
I have a table with 2 columns. Both integer. And I want to create a view with this 2 columns and add a new column (column3) equal to column1*column2
Till now everything OK.
But my column2 contains also null values, so on that rows where column2 is null, column3 will be also null.
Which is not the desired result. In that case I need that column3 to be equal with column1 like (column3 = column1*1)
Is there a nice way to do this, without running another update SP, to replace the column3 where the value is null with the value from column1??
Thanks a lot,
durug
July 12, 2002 at 1:52 pm
Try ISNULL(column1,1) * ISNULL(column2,1)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 19, 2002 at 8:08 am
I think Antares686 meant to say:
coalesce(column1,1) * coalesce(column2,1)
Matthew Galbraith
July 19, 2002 at 8:50 am
No I meant ISNULL(column1,1) * ISNULL(column2,1)
coalesce is better for multiple evaluations say if i want to output column2 if column1 is null and 0 if column2 is null. I only need 1 evaluation to take place. And in truth with coalesce you add a bit of overhead if you do it you way as if column1 is null then 1, and if 1 is null then null. So 1 is evaluated unneccessarily where isnull says if column1 is null then 1.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply