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...

CASE alias in WHERE

Hello,

I found members of this group very helpful for my last queries.
Have one problem with CASE. I can use the column name alias in Order By Clause
but unable to use it in WHERE CLAUSE.
PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.

QUERY:

SELECT
M.SECS =
CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC =0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER M
WHERE M.SECS < 100
ORDER BY M.SECS

Hoping for a immediate reply.
thanks in advanceReferences to column aliases are only valid in the ORDER BY clause. You can
work around this by putting the expression into a derived table:

SELECT secs
FROM
(SELECT secs =
CASE
WHEN no_of_sec IS NULL THEN -1
WHEN no_of_sec = 0 THEN 1
ELSE no_of_sec
END
FROM DOWNLOAD_MASTER) AS M
WHERE secs < 100
ORDER BY secs

--
David Portas
SQL Server MVP
--|||On 5 Jul 2004 04:02:38 -0700, A.V.C. wrote:

>Hello,
>I found members of this group very helpful for my last queries.
>Have one problem with CASE. I can use the column name alias in Order By Clause
>but unable to use it in WHERE CLAUSE.
>PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.
>QUERY:
>SELECT
>M.SECS =
>CASE
>WHEN NO_OF_SEC IS NULL THEN -1
>WHEN NO_OF_SEC =0 THEN 1
>ELSE NO_OF_SEC
>END
>FROM DOWNLOAD_MASTER M
>WHERE M.SECS < 100
>ORDER BY M.SECS
>
>Hoping for a immediate reply.
>thanks in advance

Hi A.V.C.,

Before answering your question, one remark about your query. I advise you
to remove "M." before "SECS". You are using an alias; not a column name.
The name "M.SECS" looks as if you're referring to a column named SECS in
the table named (or aliased) M. I expect the above query to throw an error
because column SECS can't be found in the table DOWNLOAD_MASTER. The order
by clause will probably not throw an error, but that is only because table
names (or aliases) are largely mostly disregarded by SQL Server when
evaluating an roder by clause.

To answer your question: no, this is not possible. To understand why, it
helps to know how an SQL query gets evaluated. Note that this is a
conceptual description; a good RDBMS will change the order of operation to
optimize; as long as the results remain the same that is not a problem.

Step 1: Evaluate FROM clause, build intermediate table from all rows in
the tables used, joined together on the conditions given. If old style
join syntax is used (with the ON conditions in the WHERE clause), this
step will yield the full carthesian product of all tables used.

Step 2: Evaluate WHERE clause, remove rows that don't match the criteria
from intermediate table.

Step 3: Evaluate GROUP BY clause, group rows together according to the
specified arguments.

Step 4: Evaluate HAVING clause, remove groups that don't match the
criteria from intermediate table.

Step 5: Evaluate SELECT clause, build result set to be returned from the
data in the intermediate table.

Step 6: Evaluate ORDER BY, perform sorting.

Officially, columns that are not included in the SELECT clause are not
available for sorting. Many products (like SQL Server) do allow this, but
it is a non-standard extension of the ISO/ANSI SQL-92 specification (and I
don't think that later SQL specifications included this).

Since the alias of a columns or expression is only effective from step 5
but the WHERE clause is evaluated as step 2, it is clear that an alias
can't be used in the WHERE clause.

You also ask for alternatives. In your case, you could try either "WHERE
COALESCE(NO_OF_SEC, -1) < 100" or "WHERE NO_OF_SEC < 100 OR NO_OF_SEC IS
NULL". In more complex cases, you might have to repeat the CASE expression
in the WHERE clause. If you dislike that redundancy, you can always use
the derived table approach. For your query, the equivalent with a derived
table would look like this:

SELECT SECS
FROM (SELECT SECS = CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC = 0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER) AS D
WHERE SECS < 100
ORDER BY SECS
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you David Portas and Hugo Kornelis

I agree with the point(M.) mentioned by Hugo Kornelis.
I appreciate your way of writing descriptive answers.

Thanks once again.

Carriage return within column alias

Is there a way to insert a carriage return or line feed in the middle of a column alias within a select statement? I tried using the CHAR function for the ascii value of the carriage return, but SQL Server wouldn't allow it inside the alias name for the column.
Any ideas?What are you trying to achieve with the end result? Is the result to be used in html or something? If so, you can use html tags in the header.|||The database query will be used in an ASP script run from a web site. The problem was that there were so many columns that I couldn't fit them on one page landscape for printing. If I can put some of the column headings (which are declared as aliases in my SQL query) on two lines as opposed to one long heading line, it will save page space.|||The simple solution, then is to put the HTML tag in the alias.. ie

SELECT col1 as 'COLUMN <BR> ONE'
bla bla bla

then when the column header is rendered by the asp, if it is set up correctly, it will put the break in. I believe, however, that there are ways to do this in HTML w/o the need of putting it in the column name.

Hope this helps.