Case Statement Using Table Alias, Not Possible ...?

Hello, how come I can't qualify a column with its table alias in a
case clause
this works:
select tasks.taskid,tasks.status,machinename=
case
when machinename is null ( select machine from queue where
taskid = tasks.taskid)
else machinename
end
from tasks
but when I change all machinename references to tasks.machinename
i get syntax errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'else'
For this simple query, ambiguous columns are not an issue, but when
they become one, how is one to use a case statement?"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>
You missed out the keyword "THEN" in your CASE expression.
Couldn't you do this with a JOIN? Try the following:
SELECT T.taskid, T.status,
COALESCE(T.machinename, Q.machine) AS machinename
FROM tasks AS T
LEFT JOIN queue AS Q
ON T.taskid = Q.taskid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Oh, yeah the then... at any rate, then 'then' wont fix the error.
I need a case because the query in question is part of a somewhat big
view that is now required to provide some additional "functionality".
Getting the case to work would be easier than rewriting it... well, if
it can work...
Any ideas as to why the qualifier causes it to fail?
Thanks,
Skye|||As David Portas pointed out, you need a THEN. But to avoid your error,
don't add the tasks. prefix to the machinename in the first line just before
the = sign. That is the name of the column in your result set, not the name
of the column in the tasks table. So you want:
select tasks.taskid,tasks.status,machinename=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
If for some reason, you wanted the name of the colum in the result set to be
tasks.machinename (and you almost certainly don't want that), you would have
to enclose it in single quotes, e.g.,
select tasks.taskid,tasks.status,'tasks.machinename'=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
Tom
"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>|||Tom Cooper wrote:
> As David Portas pointed out, you need a THEN. But to avoid your error,
> don't add the tasks. prefix to the machinename in the first line just befo
re
> the = sign. That is the name of the column in your result set, not the na
me
> of the column in the tasks table. So you want:
> select tasks.taskid,tasks.status,machinename=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
ahh, ok....

> If for some reason, you wanted the name of the colum in the result set to
be
> tasks.machinename (and you almost certainly don't want that), you would ha
ve
> to enclose it in single quotes, e.g.,
> select tasks.taskid,tasks.status,'tasks.machinename'=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
Thanks allot for your clarification, I actually just ended up aliasing
the case statement.
> Tom
> "MaggotChild" <hsomob1999@.yahoo.com> wrote in message
> news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...