Solved

use alise in where (MSSQL)

Posted on 2010-09-14
13
552 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 40

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 43

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 43

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 40

Accepted Solution

by:
Sharath earned 500 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 40

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

825 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