473,902 Members | 5,194 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 advance
Jul 20 '05 #1
3 22018
References 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
--
Jul 20 '05 #2
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)
Jul 20 '05 #3
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.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
47932
by: John | last post by:
Hi all, I am doing the change from having worked in Oracle for a long time to MS SQL server and am frustrated with a couple of simple SQL stmt's. Or at least they have always been easy. The SQL is pretty straightforward. I am updating a field with a Max effective dated row criteria. (PepopleSoft app) update PS_JOB as A set BAS_GROUP_ID = ' '
2
3700
by: Gunnar Vøyenli | last post by:
Hi I tried to use the alias a1 for the column f1 like this, but it fails: create table a (f1 int not null primary key) insert into a (f1) values (2) select f1 a1 from a where a1 = 2 Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'a1'.
4
28829
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the select statement). I'm mostly a SQL hobbiest, so it's possible that I am not doing this in the most efficient manner. Anyone care to comment on this with relation to the following example (is there a way to acheive this without re-stating the...
5
9670
by: Santiago Ordax Solivellas | last post by:
Hi. We have almost all our tables defined on library lib1 and some on lib2. We have alias defined on lib1 to access tables on lib2 so there is no need to qualify library name. Alias for tables on lib2 are defined this way: CREATE ALIAS lib1.table1 FOR lib2.table1; Both table owner and alias owner is the same. Tables reside on a AS/400 server and we are accesing database from a DB2 client on a PC workstation (DB2 is version 7)
2
1764
by: http://www.visual-basic-data-mining.net/forum | last post by:
Hello, I have the following tables and have setup the following sql statement as part of my data adapter. The problem is that I need to do a statement that will find the records selected in my list box. I cannot figure out how to use the "name' alias in the parameter statement. I am trying to combine first, last name, alpha number into one field for the list box and all the requests the employee made. Here are my two tables: ...
10
2401
by: Kimmo Laine | last post by:
Hi, i try to define alias and then use it in another file like this: // ---------- // file: a.cs using System; using MyAlias = MyNamespace.MyClass;
4
1785
by: Pumkin | last post by:
Hello guys, I need help in something as I don't know if it is possible what I want. I have a select like this... SELECT Cod1 as SQL, Cod2 as Oracle FROM table and I need to sort by alias SQL or Oracle as the select is composed dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the user needs to filter the data using SQL or ORACLE.
0
4233
by: Prakash_Joy | last post by:
I wanted to have a query in which i have to have an alias name in the case condition as follows : select r.column1 as Name1,t.Column2 as Name2, case @Month when 2 then t.Column1 as Jan when 3 then t.Column3 as Feb ... end from table1 t ,table2 r But it is giving syntax error for the same
11
6276
by: Rafe | last post by:
Hi, I'm working within an application (making a lot of wrappers), but the application is not case sensitive. For example, Typing obj.name, obj.Name, or even object.naMe is all fine (as far as the app is concerned). The problem is, If someone makes a typo, they may get an unexpected error due accidentally calling the original attribute instead of the wrapped version. Does anyone have a simple solution for this?
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8047
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.