Solved

use alise in where (MSSQL)

Posted on 2010-09-14
13
542 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 42

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 42

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

20 Experts available now in Live!

Get 1:1 Help Now