September 5, 2013 at 12:54 pm
Hi there. It has been a while since being on here but I have a question. I am trying to create a table that has to be backward compatible with previous versions and it is being a pain. I need to convert and age field into an age group field. Or make a new field for it. either way works.
I am trying to use a UDF because I need this to work on 6 different tables and I dont want to have to type it in all the time, also because i dont like "messy" code.
here is what i have so far.
USE [Test]
GO
/****** Object: UserDefinedFunction [dbo].[AGE_GROUP] Script Date: 09/05/2013 11:40:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN CASE @STRING
WHEN 0 THEN ' 0'
WHEN 1 THEN ' 1'
WHEN 2 THEN ' 2'
WHEN 3 THEN ' 3'
WHEN 4 THEN ' 4'
WHEN 5 THEN ' 5'
WHEN 6 THEN ' 6'
WHEN 7 THEN ' 7'
WHEN 8 THEN ' 8'
WHEN 9 THEN ' 9'
WHEN 10 THEN ' 10'
WHEN 11 THEN ' 11'
WHEN 12 THEN ' 12'
WHEN 13 THEN ' 13'
WHEN 14 THEN ' 14'
WHEN 15 THEN ' 15'
WHEN 16 THEN ' 16'
WHEN 17 THEN ' 17'
WHEN 18 THEN ' 18'
WHEN 19 THEN ' 19'
WHEN BETWEEN 20 AND 24 THEN '20-24'
END
END
There would be age groups like the "when age(between XX and xx) then 'xx-xx'
Does anyone have any ideas on how to do this?
Also it works for the 0-19 just fine.
September 5, 2013 at 2:57 pm
I have 2 ideas, one would be to change your scalar function into an inline table function that will perform much better.
CREATE FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(20))
RETURNS TABLE
BEGIN
RETURN SELECT CASE
WHEN @string = 0 THEN ' 0'
WHEN @string = 1 THEN ' 1'
WHEN @string = 2 THEN ' 2'
WHEN @string = 3 THEN ' 3'
WHEN @string = 4 THEN ' 4'
WHEN @string = 5 THEN ' 5'
WHEN @string = 6 THEN ' 6'
WHEN @string = 7 THEN ' 7'
WHEN @string = 8 THEN ' 8'
WHEN @string = 9 THEN ' 9'
WHEN @string = 10 THEN ' 10'
WHEN @string = 11 THEN ' 11'
WHEN @string = 12 THEN ' 12'
WHEN @string = 13 THEN ' 13'
WHEN @string = 14 THEN ' 14'
WHEN @string = 15 THEN ' 15'
WHEN @string = 16 THEN ' 16'
WHEN @string = 17 THEN ' 17'
WHEN @string = 18 THEN ' 18'
WHEN @string = 19 THEN ' 19'
WHEN @string BETWEEN 20 AND 24 THEN '20-24'
END
Another idea is to create a table with your age groups and join it to your queries.
September 9, 2013 at 2:05 am
You are passing in a VARCHAR(MAX) and treating it like a numeric type in your function. This results in an implicit data type conversion and also leaves you open to someone passing an invalid value to your function, namely a string that cannot be converted to a number.
Try this:
[font="Courier New"]
USE tempdb
GO
CREATE FUNCTION [dbo].[AGE_GROUP] (@age TINYINT)
RETURNS TABLE
AS
RETURN
SELECT CAST(CASE @age
WHEN 0 THEN ' 0'
WHEN 1 THEN ' 1'
WHEN 2 THEN ' 2'
WHEN 3 THEN ' 3'
WHEN 4 THEN ' 4'
WHEN 5 THEN ' 5'
WHEN 6 THEN ' 6'
WHEN 7 THEN ' 7'
WHEN 8 THEN ' 8'
WHEN 9 THEN ' 9'
WHEN 10 THEN ' 10'
WHEN 11 THEN ' 11'
WHEN 12 THEN ' 12'
WHEN 13 THEN ' 13'
WHEN 14 THEN ' 14'
WHEN 15 THEN ' 15'
WHEN 16 THEN ' 16'
WHEN 17 THEN ' 17'
WHEN 18 THEN ' 18'
WHEN 19 THEN ' 19'
WHEN 20 THEN '20-24'
WHEN 21 THEN '20-24'
WHEN 22 THEN '20-24'
WHEN 23 THEN '20-24'
END AS VARCHAR(MAX)) AS AGE_GROUP
GO
-- use it like you would a scalar-function to resolve one value
SELECT age_group
FROM [dbo].[AGE_GROUP](4)
GO
-- how to use this against a resultset:
-- build a temp table with some ages in it
SELECT CAST(1 AS TINYINT) AS age
INTO #tmp
UNION ALL
SELECT CAST(10 AS TINYINT)
UNION ALL
SELECT CAST(20 AS TINYINT);
GO
-- usage option 1 using APPLY (get to know this syntax and how APPLY works)
SELECT t.age,
grp.AGE_GROUP
FROM #tmp t
CROSS APPLY dbo.AGE_GROUP(age) grp
-- usage option 2 - functionally equivalent to usage 1 but might seem more
-- familiar because the function is used in the select-column list much
-- the same way you would write a query that uses a scalar-function
SELECT t.age,
(
SELECT AGE_GROUP
FROM dbo.AGE_GROUP(t.age)
) AS AGE_GROUP
FROM #tmp t;
GO
[/font]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply