| How to list an additional column in a result set of a minus function [message #25978] |
Thu, 29 May 2003 13:22  |
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  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
