in having clause i want to use alias name

Posted on 2009-02-17
Medium Priority
Last Modified: 2013-12-19
select claim_header_sid, count(*) cnt   from ad_clm_hdr_condition  group by claim_header_sid having cnt =1;

in having clause i want to use alias name only.Is there any other way to fix this.
Question by:vamsibat
LVL 41

Accepted Solution

Sharath earned 1920 total points
ID: 23661583
no, its not possible. If you still want to use alias only, you can try like this.
select * from (
select claim_header_sid, count(*) cnt   
  from ad_clm_hdr_condition  
 group by claim_header_sid ) t1
 where cnt =1;

Open in new window

LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 80 total points
ID: 23666813
also what is the issue for you if we use because count(*) and the alias cnt refer to the same right though the alias name is not allowed in the having clause of the same query....

select claim_header_sid, count(*) cnt  
from ad_clm_hdr_condition  
group by claim_header_sid
having count(*) =1 ;


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month15 days, 7 hours left to enroll

840 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