Home » SQL & PL/SQL » SQL & PL/SQL » accessing a select column in where clause
accessing a select column in where clause [message #193666] Mon, 18 September 2006 18:39 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hi guys,

I'm wondering if it's possible to access a select column within a where clause... perhaps it's better if I can an example of what I'm trying to do here:

select   a.column1,
         a.column2,
         (select count(b.*)
          from   tableB b
          where  b.column1 = a.column3) b_counter
from     tableA a
where    a.column4 = 'Test'
and      b_counter > 1


I'm trying to access the column, b_counter, in the where clause, but I am unsucessful. I could easily cut and paste the subquery again, but I think this is inefficient. Do you guys have any suggestions on how to access the b_counter or better yet, is there a more efficient way?

thanks for any comments/replies
Re: accessing a select column in where clause [message #193667 is a reply to message #193666] Mon, 18 September 2006 19:32 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Not sure if this is what you are looking for:

select  a.column1,
        a.column2,
	b.cnt
from tableA a, (select column1, count(*) cnt
	          from   tableB
	          group by column1) b
where	a.column4 = 'Test'
and 	a.column3 = b.column1
and	b.cnt > 1

[Updated on: Mon, 18 September 2006 20:07]

Report message to a moderator

Previous Topic: Nested table or ref.. or what else?
Next Topic: creating a stored procedure..cursor question
Goto Forum:
  


Current Time: Sun Dec 11 06:22:01 CST 2016

Total time taken to generate the page: 0.08380 seconds