?
Solved

use alise in where (MSSQL)

Posted on 2010-09-14
13
Medium Priority
?
557 Views
Last Modified: 2012-05-10
in sybase we can use alise in where

select stdid as alise_stdid from students where alise_stdid < 10084

but MSSQL server is not working .. i want a way to work for both Sybase and MSSQL with alise , its possible?
0
Comment
Question by:Deyhim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33677556
I think you have to put in .stdid as the column name, since your alias is defined as "alise_stdid"
select stdid as alise_stdid from students where alise_stdid.stdid < 10084

or potentially you meant to write this:

select stdid as alise from students where alise.stdid < 10084
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33677564
sorry... I've got it completely wrong above.   here it is corrected:

select stdid as alise_stdid from students where students.stdid < 10084
0
 
LVL 1

Author Comment

by:Deyhim
ID: 33677722
i want to use alias in where .. your solution is not doing this.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 7

Expert Comment

by:tlovie
ID: 33677760
how about this:
select s.stdid as alise_stdid from students as s where s.stdid < 10084
0
 
LVL 1

Author Comment

by:Deyhim
ID: 33677980
no i want to use it alias as alias_stdid in where ....
0
 
LVL 41

Expert Comment

by:Sharath
ID: 33678031

In MSSQL, its not possible. you can try with a sub-query like below which will work both in sybase and MSSQL.

select (select stdid as alise_stdid from students) as t1 where alise_stdid < 10084
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33678037
it won't do that... you have to alias the table name, and reference the table alias in the where clause.  your reference of alise_stdid is simply renaming the output column, the where clause can't directly use that, at least not inside of SQL92.  my final attempt at writing this is:
select alise_stdid.stdid as alise_stdid from students as alise_stdid where alise_stdid.stdid < 10084
0
 
LVL 44

Expert Comment

by:Chris Stanyon
ID: 33678287
Hi Deyhim,

You've posted this question in the MySQL zone, but then in your question you specify MS SQL. Not sure which it is, so this may be irrelevant.

In mySQL you can't use an aliased column in the WHERE clause, because the WHERE clause looks at the Raw data (and the alias doesn't exist) - you have to use HAVING.



SELECT stdid as alise_stdid
FROM students
HAVING alise_stdid < 10084;

Open in new window

0
 
LVL 1

Author Comment

by:Deyhim
ID: 33680271
Sharath_123 query is not working, if i have some thing like that it will help.

ITS MSSQL by mistake i post it in MySQL
0
 
LVL 44

Expert Comment

by:Chris Stanyon
ID: 33680747
HAVING also works in MS SQL.

That's the correct way of filtering on an aliased (or aggregate) column.


0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 33685557
check this one.
select * from (select stdid as alise_stdid from students) as t1 where alise_stdid < 10084
0
 
LVL 1

Author Closing Comment

by:Deyhim
ID: 33788466
not full
0
 
LVL 41

Expert Comment

by:Sharath
ID: 33790068
What else you are looking for?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question