Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

use alise in where (MSSQL)

Posted on 2010-09-14
13
Medium Priority
?
565 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
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month13 days, 5 hours left to enroll

971 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