use alise in where (MSSQL)

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?
LVL 1
DeyhimAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
check this one.
select * from (select stdid as alise_stdid from students) as t1 where alise_stdid < 10084
0
 
tlovieCommented:
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
 
tlovieCommented:
sorry... I've got it completely wrong above.   here it is corrected:

select stdid as alise_stdid from students where students.stdid < 10084
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DeyhimAuthor Commented:
i want to use alias in where .. your solution is not doing this.
0
 
tlovieCommented:
how about this:
select s.stdid as alise_stdid from students as s where s.stdid < 10084
0
 
DeyhimAuthor Commented:
no i want to use it alias as alias_stdid in where ....
0
 
SharathData EngineerCommented:

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
 
tlovieCommented:
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
 
Chris StanyonCommented:
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
 
DeyhimAuthor Commented:
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
 
Chris StanyonCommented:
HAVING also works in MS SQL.

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


0
 
DeyhimAuthor Commented:
not full
0
 
SharathData EngineerCommented:
What else you are looking for?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.