Wanting to get a count of users in the groups I have in my system

I have started a query as shown below that I am working to try and get to the count indicated in the subject. This query is a rehash of a query I found to print members in groups and i was going to modify it to get to the ultimate goal as the subject indicated. However when I run this, the selected variables don't appear to be showing up at all in my livereport results.select u.lastname , u.firstname , u.name , g.name from kuaf u , kuaf g where g.id in ( select id from kuafchildren start with childid = u.id connect by prior id = childid ) and u.deleted = 0 and u.type = 0 and g.deleted = 0 and g.type = 1 order by 1, 2, 3Do i need to do something to the display columns or something else to make the live report display what i am after in the above select?Thanks in advance!Jay @ PVA, Inc.

Find more posts tagged with

Comments

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Because you are using "u.name, g.name" in the select query, I'm guessing that these 2 columns aren't appearing as expected in the results, correct? If so, try using:

u.name "U Name", g.name "G Name"

...instead of

u.name, g.name

...in your select statement. I assume you are using the "AutoLiveReport" report format.

- Bhupinder

------------------------------------------------------
Bhupinder Singh, B.Math., B.Ed.
Senior Product Specialist, Customer Support
Open Text Corporation, Waterloo, Ontario, Canada
Customer support e-mail: support@opentext.com
Customer Support Telephone: 800-540-7292
------------------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, June 30, 2006 3:25 PM
To: eLink Recipient
Subject: Wanting to get a count of users in the groups I have in my system

Wanting to get a count of users in the groups I have in my system

Posted by pva_inc (PVA, PVA) on 06/30/2006 03:21 PM

I have started a query as shown below that I am working to try and get to the count indicated in the subject. This query is a rehash of a query I found to print members in groups and i was going to modify it to get to the ultimate goal as the subject indicated. However when I run this, the selected variables don't appear to be showing up at all in my livereport results.

select u.lastname , u.firstname , u.name , g.name from kuaf u , kuaf g where g.id in ( select id from kuafchildren start with childid = u.id connect by prior id = childid ) and u.deleted = 0 and u.type = 0 and g.deleted = 0 and g.type = 1 order by 1, 2, 3

Do i need to do something to the display columns or something else to make the live report display what i am after in the above select?

Thanks in advance!

Jay @ PVA, Inc.

Pfizer_Developers

Hi Jay:First issue: Anytime you have a join of two tables or more, you need the join condition: That is missing from your sql.The formula is: for n number of tables in the from clause you need n-1 join conditions in the where clause. So you must have a where condition which joins the "u" and the "g" tables.Next:Do you want to list the count of the "direct" members of groups, or do you want to scan the subgroups too?With that I mean if a group "Ag" has two members "Au" and "Bg" where "Au" is the user and "Bg" is another group.In your count, do you want to attribute the members of the "Bg" group to the "Ag" group? If so then you have to write a hierarchical query, if not then a simple sql will suffice.Hope this helps.P.S. looking at the sql quickly, I think something is not quite right. I'll take a look at it later.

Pfizer_Developers

Try this sql and let me know if this is what you are looking for:SELECT kg.ID, kg.NAME, kg.TYPE, kg.deleted, decode(ku.type,0,'Users',1,'Sub-Group',ku.type) as "Member Type", COUNT (*) FROM kuaf kg, kuaf ku, kuafchildren kc WHERE kc.childid = ku.ID AND kc.ID = kg.ID AND kg.TYPE = 1 group by kg.id, kg.name, kg.type, kg.deleted, decode(ku.type,0,'Users',1,'Sub-Group',ku.type) order by id, name, type, deleted, "Member Type" This will provide you with a list of all groups and the number of "immediate users" and "immediate sub-groups" belonging to the group in your Livelink instance.The "Member Type" column will list "Users" or "Sub-Group" with a count for each type for each of the group.Please let me know if this helps.Regards,Mohsin

PVA_PVA_(pva_inc_-_(deleted))

Mohsin,Thanks verrry much! That gave me the information that I needed.Regards!Jay

PVA_PVA_(pva_inc_-_(deleted))

Thank you much for the suggestion regarding the AutoLiveReport!That plus the sql sample the other gentleman gave me what I needed.Regards,Jay>...in your select statement. I assume you are using the >"AutoLiveReport" report format. >- Bhupinder