Solved

use alise in where (MSSQL)

Posted on 2010-09-14
13
549 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
 
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
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: 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

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.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=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.wikiped…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now