May 4, 2015 at 6:31 pm
Hello,
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN
DECLARE @male VARCHAR(10) = 'Mr'
DECLARE @female VARCHAR(10) = ('Ms.', 'Mrs.')
DECLARE @unknown VARCHAR(10) <> ('Mr', 'Ms.', 'Mrs.')
SELECT @male = 'male'
WHEN 'Mr' THEN 'Male'
WHEN ('Ms.', 'Mrs.') THEN 'Female'
ELSE 'Unknown'
Thanks in advance for your help!
RedMittens
May 4, 2015 at 8:16 pm
CREATE TABLE dbo.GenderTitle (
Title varchar(50) PRIMARY KEY,
Gender varchar(20) not null
)
INSERT INTO dbo.GenderTitle
(Title, Gender)
SELECT 'Mr', 'Male'
UNION
SELECT 'Ms.', 'Female'
UNION
SELECT 'Mrs.', 'Female'
SELECT *, ISNULL(Gender, 'Unknown') Gender
FROM dbo.Person P
LEFT JOIN dbo.GenderTitle GT ON GT.Title = P.Title
Hope it helps.
_____________
Code for TallyGenerator
May 4, 2015 at 9:25 pm
I'm trying to create a function, not a new table.
May 4, 2015 at 9:34 pm
You could do something like this:
CREATE FUNCTION dbo.Gender
(
@titleofcourtesy AS VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
SELECT
CASE
WHEN @titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN @titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
If you wonder why I'm suggesting a table-valued function, read the following article: http://www.sqlservercentral.com/articles/T-SQL/91724/
May 4, 2015 at 9:46 pm
I'm trying to create a function, not a new table.
- mistake #1
hardcoding data in a routine code - mistake #2
Implementing relations between entities (title and gender) in conditional procedural code instead of a table - mistake #3.
3 basic mistakes for such a small piece of functionality - looks too much to me.
Do not forget what "Q" in "T-SQL" means - query.
You are supposed to query a data storage, not generate the data on fly.
And yes, using functions in T-SQL is a mistake.
Functions are extremely ineffective in data querying and must be avoided by all means.
_____________
Code for TallyGenerator
May 5, 2015 at 8:52 am
Sergiy (5/4/2015)
I'm trying to create a function, not a new table.
- mistake #1
hardcoding data in a routine code - mistake #2
Implementing relations between entities (title and gender) in conditional procedural code instead of a table - mistake #3.
3 basic mistakes for such a small piece of functionality - looks too much to me.
Do not forget what "Q" in "T-SQL" means - query.
You are supposed to query a data storage, not generate the data on fly.
And yes, using functions in T-SQL is a mistake.
Functions are extremely ineffective in data querying and must be avoided by all means.
I agree in your first statements because there should not be a relation between title and gender because they're different attributes of a person (person is the entity, title and gender are attributes). However, you suggested to keep the same relation but using a table. That might cause something like this one day: http://www.cambridge-news.co.uk/Cambridge-paediatrician-8217-s-outrage-Pure-Gym/story-26188693-detail/story.html
Or it could give you problems if someone wants to include 7 options for gender: http://theweek.com/speedreads/457472/only-america-7-gender-options
I also have a problem with your last sentence as I don't believe in absolute truths on SQL. An inline table-valued function won't suffer from performance problems if done right.
May 5, 2015 at 4:30 pm
Luis Cazares (5/5/2015)
I agree in your first statements because there should not be a relation between title and gender because they're different attributes of a person (person is the entity, title and gender are attributes). However, you suggested to keep the same relation but using a table. That might cause something like this one day: http://www.cambridge-news.co.uk/Cambridge-paediatrician-8217-s-outrage-Pure-Gym/story-26188693-detail/story.html
Or it could give you problems if someone wants to include 7 options for gender: http://theweek.com/speedreads/457472/only-america-7-gender-options
I don't wanna jump into discussion about reasonability of the business rule given to OP.
It may be brilliant or totally stupid and irrelevany, but it's BA's job to define rules (yes, they may be pretty stupid too, unfortunately).
I was talking stricktly about an implementation of the given rule.
The rule defines the relation between person's title and gender.
Therefore it must be implemented in the database exactly like that: relation between gender and title.
I agree - my proposed solution is too simplistic, I would not do it like that in my own database.
But it seems to be too complicated for OP anyway.
I also have a problem with your last sentence as I don't believe in absolute truths on SQL. An inline table-valued function won't suffer from performance problems if done right.
Inline table-valued functions suffer from the same performance problems as scalar ones.
The only difference is that table functions due to their nature are mainly used in FROM clause (executed once per statement), and scalar functions are called from SELECT (and WHERE :w00t:) clauses (executed once per row).
That's what makes the difference.
But using a view would be more effective than ITV anyway.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply