Home » SQL & PL/SQL » Client Tools » How to list an additional column in a result set of a minus function
How to list an additional column in a result set of a minus function [message #25978] Thu, 29 May 2003 13:22 Go to next message
Rabeeha Khan
Messages: 11
Registered: May 2003
Junior Member
I have a query that gives me a set of missing function groups for a particular user:

((select function_group_id from
euserclass_template_details
where userclass_id='INTERNAL_LEHMAN')
minus
(select c.function_group_id from
euser_function_group c, euserclass_template_details d
where c.function_group_id=d.function_group_id
and d.userclass_id='INTERNAL_LEHMAN'
and c.user_id='98414'))

I would like to know when I report this, how to do list the user_id with each function_group. This is the simple case. I would eventually like to query all the user ids in a table. But for now this should be enough. Please help.
Re: How to list an additional column in a result set of a minus function [message #25979 is a reply to message #25978] Thu, 29 May 2003 13:29 Go to previous messageGo to next message
Rabeeha Khan
Messages: 11
Registered: May 2003
Junior Member
I tried this:

select a.user_id, b.function_group_id
from euser_profile a,
((select function_group_id from
euserclass_template_details
where userclass_id='INTERNAL_LEHMAN')
minus
(select c.function_group_id from
euser_function_group c, euserclass_template_details d
where c.function_group_id=d.function_group_id
and d.userclass_id='INTERNAL_LEHMAN'
and c.user_id=a.user_id)) b;

However, the subquery doesn't recognize the a.user_id as a proper column. How can ensure the join between these two tables?
Re: Got it to work , but I have a performance question [message #25980 is a reply to message #25978] Thu, 29 May 2003 14:06 Go to previous message
Rabeeha Khan
Messages: 11
Registered: May 2003
Junior Member
Ok I got it to work -

(select a.user_id, b.function_group_id from
euser_profile a, euserclass_template_details b
where userclass_id='INTERNAL_LEHMAN'
and pk_misc_procs.IS_INTERNAL_USER_ONLY_BOUND(a.user_id)='Y')
minus
(select c.user_id, c.function_group_id from
euser_function_group c, euserclass_template_details d
where c.function_group_id=d.function_group_id
and d.userclass_id='INTERNAL_LEHMAN'
)

Is there a better way to word this , from a performance pt of view? This query will result in about 650,000+ rows.
Previous Topic: Null
Next Topic: Delete Duplicate Records from Table
Goto Forum:
  


Current Time: Wed Aug 05 20:54:42 CDT 2020