Oracle Query to list users from a particular group

[Chris Thornhill]

Hi all,

I've got a strange situation where i have a query which works in both our DEV and QA environments, and worked in PROD as well for the first week.  Now this query is throwing an internal server error rather than providing the info we need.  I have no idea as to why it continues to work in both other environments, or why it was working for a short period of time.  I hope the issue is simply that the query itself is problematic, and can be tackled in another fashion.  This is where i'm hoping the experts can shed some light :)

In QA:
SELECT ID, Last_name, First_name FROM CMN_SEC_USERS where id in (
SELECT user_id FROM CMN_SEC_USER_GROUPS WHERE GROUP_ID IN
(SELECT ID FROM CMN_SEC_GROUPS WHERE
(GROUP_CODE='OCIO_FILE_REV_EXEC_ANA'))
)

Currently in PROD, as i have tried a number of changes without success:
SELECT
@SELECT:u.id:user_id@,
@SELECT:u.last_name:last_name@,
@SELECT:u.first_name:first_name@,
@SELECT:r.full_name:full_name@
FROM
CMN_SEC_USERS u,
SRM_RESOURCES r
WHERE
u.id = r.user_id
AND
u.id IN (SELECT user_id FROM CMN_SEC_USER_GROUPS WHERE GROUP_ID=
(SELECT ID FROM CMN_SEC_USER_GROUPS WHERE GROUP_ID='OCIO_FILE_REV_EXEC_ANA'))
AND
@FILTER@

Any help would be appreciated...thanks!

[David Morton]

When you get that internal server error you need to look in the app_ca.log file for the detailed error and you should be able to spot an ORA- error somewhere in there.

However I suspect the problem might be mixing up your CMN_SEC_GROUPS and CMN_SEC_USER_GROUPS tables - in your SQL you use CMN_SEC_GROUPS in you final subselect but in the NSQL you use CMN_SEC_USER_GROUPS and I would guess that that is returning multiple rows and that is causing the ORA error. 

[Chris Thornhill]

Thanks for that, David.

Quite right...it appears in all my updates to get it to work, i created a little bit of a mess with the last portion of the query.

In QA, it works, but the client would like to see the full name appear rather than first or last.  CMN_SEC_USERS doesn't have a populated Full Name field, so i needed to grab it elsewhere.  This looks like it works, but the final aspect is to limit it to those within a specific group, so the client doesn't have to sort through hundreds of names, and potentially select someone who isn't in that specific role.

The app_ca log shows ORA-0722:invalid number as an error, but i wasn't able to figure out where that invalid number is coming from.

But...thanks to your point...i did manage to fix it.  So thanks for that!  It was an issue of mixing up the groups, as you mentioned.

This now works as expected:
SELECT
@SELECT:u.id:user_id@,
@SELECT:u.last_name:last_name@,
@SELECT:u.first_name:first_name@,
@SELECT:r.full_name:full_name@
FROM
CMN_SEC_USERS u,
SRM_RESOURCES r
WHERE
u.id = r.user_id
AND
u.id IN ( SELECT user_id FROM CMN_SEC_USER_GROUPS WHERE GROUP_ID =
(SELECT ID FROM CMN_SEC_GROUPS WHERE
GROUP_CODE='OCIO_FILE_REV_EXEC_ANA'))
AND
 
Again...thanks so much for taking the time to look it over and spot the trouble.  Tinkering with it for too long left me blind to the issue.

[David Morton]

CMN_SEC_USER_GROUPS.GROUP_ID is a numeric column, so your; 

WHERE GROUP_ID='OCIO_FILE_REV_EXEC_ANA' 

would have raised that ORA-0722:invalid number error.