| Using Alias in the Same SELECT statement [message #521727] |
Thu, 01 September 2011 10:09  |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Hi,
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same
select statement.
Is there a way to use alias within the same select statement?
Your prompt response is greatly appreciated.
Thanks
|
|
|
|
|
|
|
|
| Re: Using Alias in the Same SELECT statement [message #521730 is a reply to message #521727] |
Thu, 01 September 2011 10:25  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
No, you cannot use an alias in the where clause.
Either use:
Select dept_id, agency, sum(quantity) as "sum_qty" from department
group by dept_id, agency
having sum(quantity) > 500;
or
select *
from ( Select dept_id, agency, sum(quantity) as "sum_qty" from department group by dept_id, agency )
where "sum_qty" > 500;
Regards
Michel
[Updated on: Thu, 01 September 2011 10:36] Report message to a moderator |
|
|
|
| Re: Using Alias in the Same SELECT statement [message #521734 is a reply to message #521728] |
Thu, 01 September 2011 10:32 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Impossible in one query, as also stated in SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065646 (after searching for C_ALIAS) Quote:Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.
Also, as spotted by BlackSwan, when using non-aggregates and aggregates together, you have to use GROUP BY clause.
Two possible workarounds:
1) Use aggregate in HAVING clause: select dept_id, agency, sum(quantity) as "sum_qty"
from department
group by dept_id, agency
having sum(quantity) > 500;
2) Using subquery: select dept_id, agency, "sum_qty"
from (
select dept_id, agency, sum(quantity) as "sum_qty"
from department
group by dept_id, agency )
where "sum_qty" > 500;
Note that when enclosing colun name between double quotes, it becomes case sensitive, but standard identifiers (without double quotes) are in upper case.
|
|
|
|
| Re: Using Alias in the Same SELECT statement [message #521735 is a reply to message #521734] |
Thu, 01 September 2011 11:04 |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Thank you but I have another scenario where I use Rank() function
for ex:
select dept_id, dept_name, rank() over(partition by dept_id, order by sum(employees)) as emp_rank
from department
In this situation I can not use Rank() in the HAVING clause.
My results for this query are as follows
dept_id, dept_name, emp_rank
-------- --------- ----------
10 ACCOUNTS 1
10 FINANCE 2
20 PAYROLL 1
20 MARKETING 2
I want to display only records whose emp_rank = 1
ie. the results should be
dept_id, dept_name, emp_rank
-------- --------- ----------
10 ACCOUNTS 1
20 PAYROLL 1
|
|
|
|
|
|
|
|
| Re: Using Alias in the Same SELECT statement [message #521741 is a reply to message #521734] |
Thu, 01 September 2011 11:33 |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
If you have SUM function it works, I have another situaation where it uses RANK in the select statement
when I tried to use RANK in HAVING clause it says "window functions are not allowed here".
Any ideas how to get around this.
Or
Is there any way to filter records by specifying criteria in SELECT cluase
Ex:
SELECT DEPT_ID, DEPT_NAME RANK() OVER(PARTITION BY DEPT_ID ORDER BY DEPT_NAME as EMPLOYEE_RANK FROM DEPARTMENT
The results are as follows:
Dept_id Dept_Name employee_rank
------- ---------- ---------------
10 ACCOUNTS 1
20 HR 2
30 MARKETING 1
40 PAYROLL 2
My results should be
Dept_id Dept_Name employee_rank
------- ---------- ---------------
10 ACCOUNTS 1
30 MARKETING 1
Basically I want to display only those records whose employee_rank = 1
Any ideas?
|
|
|
|
|
|
|
|
| Re: Using Alias in the Same SELECT statement [message #521745 is a reply to message #521737] |
Thu, 01 September 2011 11:53 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 01 September 2011 12:15rank is an analytic.
Rank can be analytic or aggregate. Aggregate rank example:
SQL> select job,
2 rank(2000) within group (order by sal desc) "rank of 2000"
3 from emp
4 group by job
5 /
JOB rank of 2000
--------- ------------
ANALYST 3
CLERK 1
MANAGER 4
PRESIDENT 2
SALESMAN 1
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
