tomhwtt
asked on
CASE statment in Computed Column Specification
Hi..
I have an MS SQL database(SQL SERVER 2005). I have two columns I want to add together and have no problem doing this in the Computed Column properties with the following.
([itemHandlingPer] + [itemShippingPer])
Now, what if I only want them to add up IF [columnA] = Whatever
I figured I would have to use CASE or something, but I am so new to MS SQL that I cannot seem to figure it out.
Thank You
Tom
I have an MS SQL database(SQL SERVER 2005). I have two columns I want to add together and have no problem doing this in the Computed Column properties with the following.
([itemHandlingPer] + [itemShippingPer])
Now, what if I only want them to add up IF [columnA] = Whatever
I figured I would have to use CASE or something, but I am so new to MS SQL that I cannot seem to figure it out.
Thank You
Tom
ASKER
Very powerful stuff! Thank You.
OK..so I have them set as money instead of varchar.
How do I declare the function inside the definition of the calculated column?
Thanks for the quick response!
Tom
OK..so I have them set as money instead of varchar.
How do I declare the function inside the definition of the calculated column?
Thanks for the quick response!
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ Tom
<<OK..so I have them set as money instead of varchar. >>
simply change the varchar to data type money.
<<How do I declare the function inside the definition of the calculated column?>>`here is an example
CREATE TABLE Contact(
itemHandlingPer money,
itemShippingPer money,
totalvalue as dbo.fn_calculatedcolumn5(i temHandlin gPer, itemShippingPer)
)
@acperkins
<<Since it is a computed column, I would not resort to using a UDF to do this, but instead just do:>>
Is there a specific reason for that? Functions are specifically designed for encapsulation.
Udf are a nice clean way to keep track of computed columns and formulas. By default, I think it is a good practice to keep them in functions. When the number of computed columns increases and it is easier to maintain and encapsulate logic onto. IMHO
<<OK..so I have them set as money instead of varchar. >>
simply change the varchar to data type money.
<<How do I declare the function inside the definition of the calculated column?>>`here is an example
CREATE TABLE Contact(
itemHandlingPer money,
itemShippingPer money,
totalvalue as dbo.fn_calculatedcolumn5(i
)
@acperkins
<<Since it is a computed column, I would not resort to using a UDF to do this, but instead just do:>>
Is there a specific reason for that? Functions are specifically designed for encapsulation.
Udf are a nice clean way to keep track of computed columns and formulas. By default, I think it is a good practice to keep them in functions. When the number of computed columns increases and it is easier to maintain and encapsulate logic onto. IMHO
I agree they are a good way to encapsulate logic, however there is overhead involved in scalar UDF's:
Are UDFs Harmful to SQL Server Performance?
http://www.sql-server-performance.com/articles/per/sql_server_udfs_p1.aspx
Proposed Solution to the Performance Problem with SQL Server Scalar UDFs
http://www.novicksoftware.com/Articles/sql-server-udf-performance-problem/sql-server-udf-performance-problem.htm
Performance impact: SQL trace and user defined scalar functions
http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx
So I would argue that unless this same formula is going to be used elsewhere then a simple case is all that is needed.
Are UDFs Harmful to SQL Server Performance?
http://www.sql-server-performance.com/articles/per/sql_server_udfs_p1.aspx
Proposed Solution to the Performance Problem with SQL Server Scalar UDFs
http://www.novicksoftware.com/Articles/sql-server-udf-performance-problem/sql-server-udf-performance-problem.htm
Performance impact: SQL trace and user defined scalar functions
http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx
So I would argue that unless this same formula is going to be used elsewhere then a simple case is all that is needed.
Thanks for the articles you pointed out. One of them indicates...
<<This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. >>>>
In other words, the overhead created by UDF doing few or no IO's does not justifiy discarding them in the context of the current question. Enpasulating them or not has no performance impact in this case.
<<I agree they are a good way to encapsulate logic>>
That is only *one* good aspect of using them in calculated columns. Here is another: A function that does not do any IO's from tables (such as in this case) may actually do better caching on heavily accessed columns. That is not a neglectable advantage.
<<however there is overhead involved in scalar UDF's:>>
These are *general* drawbacks for functions accessing tables and querying them. My opinion is that, on the *overall* the cautious use of functions can have strong advantages in terms of maintainability and caching.
But where I agree with you is the fact there is a need to avoid functions that cause heavy IO's. In a sense, functions are a bit like triggers: the fact they can easily get forgotten or do not allow the user to immediately perceive their impact seems to be their main weakness.
Regards...
<<This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. >>>>
In other words, the overhead created by UDF doing few or no IO's does not justifiy discarding them in the context of the current question. Enpasulating them or not has no performance impact in this case.
<<I agree they are a good way to encapsulate logic>>
That is only *one* good aspect of using them in calculated columns. Here is another: A function that does not do any IO's from tables (such as in this case) may actually do better caching on heavily accessed columns. That is not a neglectable advantage.
<<however there is overhead involved in scalar UDF's:>>
These are *general* drawbacks for functions accessing tables and querying them. My opinion is that, on the *overall* the cautious use of functions can have strong advantages in terms of maintainability and caching.
But where I agree with you is the fact there is a need to avoid functions that cause heavy IO's. In a sense, functions are a bit like triggers: the fact they can easily get forgotten or do not allow the user to immediately perceive their impact seems to be their main weakness.
Regards...
I wouldn't use a UDF in this scenario. The problem with scalar-UDFs is that it's far too easy for someone to come along and decide that it should look in a table for something, and that can have disastrous effects.
And even if I did, I wouldn't've used @function_out in there... I'd've just returned in directly.
Rob
And even if I did, I wouldn't've used @function_out in there... I'd've just returned in directly.
Rob
<<he problem with scalar-UDFs is that it's far too easy for someone to come along and decide that it should look in a table for something, and that can have disastrous effects.>>
I understand. I prefer to thrust my security plan from preventing that from happening. Somebody who ignores the effect of a specific SQL functionnality should not have access to DDL definitions in the first place.
<<And even if I did, I wouldn't've used @function_out in there... I'd've just returned in directly.>>
Good for you. ;)
I understand. I prefer to thrust my security plan from preventing that from happening. Somebody who ignores the effect of a specific SQL functionnality should not have access to DDL definitions in the first place.
<<And even if I did, I wouldn't've used @function_out in there... I'd've just returned in directly.>>
Good for you. ;)
ASKER
Thank You! Just what I was looking for.
create function fn_calculatedcolumn5(@item
returns varchar(20)
as
begin
declare @function_out varchar(20)
select @function_out =
case
when @itemHandlingPer = '1' then (@itemHandlingPer + @itemShippingPer)
when @itemHandlingPer = '2' then @itemHandlingPer
else @itemShippingPer
end
return @function_out
end
Second, you can declare the function inside the definition of the calculated column.
PS: before adding values don't forget to CAST them explicitely if they re not of the same initial type. Especially if you store numeric values as varchar.
HTH