August 2, 2018 at 7:03 am
Hi there everyone
I'm trying to use an ALIAS table name in a CASE statement, but I'm hitting problems. Any help would be much appreciated - thank you.
I've used the datepart(month,DATEOFBIRTH) AS MTH_NUM statement to obtain the month part of the DATEOFBIRTH column as a number. I then want to use the data in the MTH_NUM column within a CASE statement to produce something like this:
CASE MTH_NUM
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
END
However, when I run the CASE statement as part of my overall SQL query I get told that MTH_NUM is an invalid column name.
Does anyone know how I could get around this?
Many thanks
Jon
August 2, 2018 at 7:17 am
CTE or subquery only, or use the expression instead of the alias
Easiest, tbh, would be
CASE datepart(month,DATEOFBIRTH)
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
END
Column aliases can't be used in the query that they're defined in, except for the ORDER BY clause, the only clause that runs after the SELECT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2018 at 7:18 am
Jon
No, you can't do that, since MTH_NUM is not guaranteed to have been evaluated when the CASE expression is evaluated. You'll either need to repeat the whole DATEPART expression, or use a CTE or subquery to ensure that MTH_NUM is evaluated first.
John
August 2, 2018 at 7:28 am
Thank you both. I've got this to work now.
Best wishes
Jon
August 2, 2018 at 10:24 am
GilaMonster - Thursday, August 2, 2018 7:17 AMCTE or subquery only, or use the expression instead of the alias
Easiest, tbh, would be
CASE datepart(month,DATEOFBIRTH)
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
ENDColumn aliases can't be used in the query that they're defined in, except for the ORDER BY clause, the only clause that runs after the SELECT
Actually you can do what the OP is trying to do by using the "CROSS APPLY VALUES" method... Something like the following...SELECT
CASE mn.MONTH_NUM
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
END
FROM
dbo.TableName tn
CROSS APPLY ( VALUES (DATEPART(MONTH, tn.DATEOFBIRTH)) ) mn (MONTH_NUM);
That said, There's no real advantage to using the more verbose syntax (in this specific case) but it does work.
August 2, 2018 at 12:11 pm
SELECT in the CROSS APPLY is more flexible, in that it makes it easier to assign multiple alias names and allows for table access to be part of assigning an alias as well. In the broader sense, this method has other advantages, particularly that CROSS APPLYs can "cascade", that is, you can use the alias of an earlier CROSS APPLY(s) in a later CROSS APPLY(s):
CROSS APPLY ( SELECT DATEPART(MONTH, tn.DATEOFBIRTH) AS MONTH_NUM ) AS ca1
CROSS APPLY ( SELECT MONTH_NUM % 12 + 1 AS NEXT_MONTH_NUM ) AS ca2
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 2, 2018 at 12:43 pm
ScottPletcher - Thursday, August 2, 2018 12:11 PMSELECT in the CROSS APPLY is more flexible, in that it makes it easier to assign multiple alias names and allows for table access to be part of assigning an alias as well. In the broader sense, this method has other advantages, particularly that CROSS APPLYs can "cascade", that is, you can use the alias of an earlier CROSS APPLY(s) in a later CROSS APPLY(s):
CROSS APPLY ( SELECT DATEPART(MONTH, tn.DATEOFBIRTH) AS MONTH_NUM ) AS ca1
CROSS APPLY ( SELECT MONTH_NUM % 12 + 1 AS NEXT_MONTH_NUM ) AS ca2
The same can be accomplished using CROSS APPLY VALUES...CROSS APPLY ( VALUES (DATEPART(MONTH, tn.DATEOFBIRTH)) ) ca1 (MONTH_NUM)
CROSS APPLY ( VALUES (cal.MONTH_NUM % 12 + 1) ) ca2 (NEXT_MONTH_NUM)
August 2, 2018 at 1:42 pm
Jason A. Long - Thursday, August 2, 2018 12:43 PMScottPletcher - Thursday, August 2, 2018 12:11 PMSELECT in the CROSS APPLY is more flexible, in that it makes it easier to assign multiple alias names and allows for table access to be part of assigning an alias as well. In the broader sense, this method has other advantages, particularly that CROSS APPLYs can "cascade", that is, you can use the alias of an earlier CROSS APPLY(s) in a later CROSS APPLY(s):
CROSS APPLY ( SELECT DATEPART(MONTH, tn.DATEOFBIRTH) AS MONTH_NUM ) AS ca1
CROSS APPLY ( SELECT MONTH_NUM % 12 + 1 AS NEXT_MONTH_NUM ) AS ca2The same can be accomplished using CROSS APPLY VALUES...
CROSS APPLY ( VALUES (DATEPART(MONTH, tn.DATEOFBIRTH)) ) ca1 (MONTH_NUM)
CROSS APPLY ( VALUES (cal.MONTH_NUM % 12 + 1) ) ca2 (NEXT_MONTH_NUM)
SELECT is needed sometimes, so I just tend to stick with it unless I specifically need VALUES(). For example, suppose you wanted to return 1 row for each value of the month in the derived month value:
CROSS APPLY (
SELECT t.number /*I use "number" as the name rather than just "N" -- I never use single-letter variable "names"*/
FROM dbo.tally t
WHERE t.number <= DATEPART(MONTH, tn.DATEOFBIRTH)
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 3, 2018 at 8:45 am
j.clay 47557 - Thursday, August 2, 2018 7:03 AMHi there everyoneI'm trying to use an ALIAS table name in a CASE statement, but I'm hitting problems. Any help would be much appreciated - thank you.
I've used the datepart(month,DATEOFBIRTH) AS MTH_NUM statement to obtain the month part of the DATEOFBIRTH column as a number. I then want to use the data in the MTH_NUM column within a CASE statement to produce something like this:
CASE MTH_NUM
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
ENDHowever, when I run the CASE statement as part of my overall SQL query I get told that MTH_NUM is an invalid column name.
Does anyone know how I could get around this?
Many thanks
Jon
>> I'm trying to use an ALIAS table name in a CASE statement [sic], but I'm hitting problems. Any help would be much appreciated - thank you. <<
There is no such thing as a CASE statement in SQL! We have a CASE expression instead. Expressions return single scalar values.
>> I've used the datepart(month,DATEOFBIRTH) AS MTH_NUM statement to obtain the month part of the DATEOFBIRTH column as a number. I then want to use the data in the MTH_NUM column within a CASE statement to produce something like this: <<
You can use the datapart() function as the control in the CASE expression. There is no need to create a local variable. This is because SQL has strong orthogonality. But since you didn't bother to post more code, I have no idea what you're getting a particular error.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 3, 2018 at 9:26 am
jcelko212 32090 - Friday, August 3, 2018 8:45 AMj.clay 47557 - Thursday, August 2, 2018 7:03 AMHi there everyoneI'm trying to use an ALIAS table name in a CASE statement, but I'm hitting problems. Any help would be much appreciated - thank you.
I've used the datepart(month,DATEOFBIRTH) AS MTH_NUM statement to obtain the month part of the DATEOFBIRTH column as a number. I then want to use the data in the MTH_NUM column within a CASE statement to produce something like this:
CASE MTH_NUM
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE 'Leave'
ENDHowever, when I run the CASE statement as part of my overall SQL query I get told that MTH_NUM is an invalid column name.
Does anyone know how I could get around this?
Many thanks
Jon>> I'm trying to use an ALIAS table name in a CASE statement [sic], but I'm hitting problems. Any help would be much appreciated - thank you. <<
There is no such thing as a CASE statement in SQL! We have a CASE expression instead. Expressions return single scalar values.
>> I've used the datepart(month,DATEOFBIRTH) AS MTH_NUM statement to obtain the month part of the DATEOFBIRTH column as a number. I then want to use the data in the MTH_NUM column within a CASE statement to produce something like this: <<
You can use the datapart() function as the control in the CASE expression. There is no need to create a local variable. This is because SQL has strong orthogonality. But since you didn't bother to post more code, I have no idea what you're getting a particular error.
Unless you are using Oracle. There the CASE can be an expression used in a SELECT, INSERT, UPDATE, or DELETE statement OR it may also be used as a control flow statement like and IF.
Most of us here actually understood what was meant by the OP. Why don't you go do something useful, like join a different forum site.
August 3, 2018 at 3:19 pm
j.clay 47557 - Thursday, August 2, 2018 7:03 AMMost of us here actually understood what was meant by the OP. Why don't you go do something useful, like join a different forum site.
I've been teaching SQL for three decades now and I found a few things that are important. One of the basic things is getting the student to understand the difference between an expression and a statement. This is especially important in declarative languages. This is why I stress that CASE is an expression and not a statement; I found that once they get this concept so much of the rest of their coding becomes easier.
What has your experience been, in all the decades you've taught SQL? In your books, and in the response your readers have sent you over the decades?
As another one of my pedantic asides, I was the guy that wanted to see the SQL/PSM have a three-way execution control statement of the form:
IF <conditional expression> THEN <true clause>
[ [ELSE <false clause>]
OTHERWISE<unknown clause>];
Following the usual SQL conventions that {false, unknown} would be treated the same in the DML but not in the DDL. It's really messy as you get into it.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 3, 2018 at 3:26 pm
jcelko212 32090 - Friday, August 3, 2018 8:45 AMj.clay 47557 - Thursday, August 2, 2018 7:03 AMMost of us here actually understood what was meant by the OP. Why don't you go do something useful, like join a different forum site.
I've been teaching SQL for three decades now and I found a few things that are important. One of the basic things is getting the student to understand the difference between an expression and a statement. This is especially important in declarative languages. This is why I stress that CASE is an expression and not a statement; I found that once they get this concept so much of the rest of their coding becomes easier.
What has your experience been, in all the decades you've taught SQL? In your books, and in the response your readers have sent you over the decades?
As another one of my pedantic asides, I was the guy that wanted to see the SQL/PSM have a three-way execution control statement of the form:
IF <conditional expression> THEN <true clause>
[ [ELSE <false clause>]
OTHERWISE<unknown clause>];
Following the usual SQL conventions that {false, unknown} would be treated the same in the DML but not in the DDL. It's really messy as you get into it.
You know, I can get pedantic at times as well. I tend to do it in person, not as faceless person hiding behind the internet. And usually it isn't about SQL but soccer (football everywhere else in the world) and that large marked area in front of each goal. Had it beat in to my by a fellow soccer referee, and again that was in person.
I am tired of you patting yourself on the back all the time, had enough of that from my professors in college. Perhaps you should go join them. I will use the terms that people understand to help them grasp a concept and then work on them getting the terms correct.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply