Find all users and their all group in one table without input

I'm using LL8.1.5 and SQL 7.I'm trying to return a table in which the first column contains the user's lastname, firstname, the second column contains the name of the user's Base Group which is unique, the third column contains the names of the user's other groups which depend on different user: some users have none while some users have a list of 2,3,4,or 8 groups. It's OK for me to solve it with input. And I read a solution from Alex Kowalenko, it still needs an input. 1.So is it possible to solve it in one table in LiveReport? 2.What's wrong with my SQL code since it only returns Base Group?select a.lastname, a.firstname, a.name from kuaf a where a.deleted = 0 and a.type = 1 and a.ownerid in (select b.childid from kuafchildren b where b.childid in (select c.rlid from kuafrightslist)Thank you very much!

Find more posts tagged with

Comments

eLink User

Message from Alex Kowalenko via eLinkShirley, Is this what you want?One table for ALL users; for example:User Name Base Group Other Groups------------------+------------+-------------------------Xie, Shirley GE Nuclear IT Team, Livelink UserKowalenko, Alex Open Text PS, Ottawa, North America...etc. for all users in your Livelink database...-alex------Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Tuesday, May 08, 2001 11:59To: eLink RecipientSubject: Find all users and their all group in one table without inputFind all users and their all group in one table without inputPosted by xie on 05/08/2001 11:54 AMI'm using LL8.1.5 and SQL 7.I'm trying to return a table in which the first column contains the user'slastname, firstname, the second column contains the name of the user's BaseGroup which is unique, the third column contains the names of the user'sother groups which depend on different user: some users have none while someusers have a list of 2,3,4,or 8 groups.It's OK for me to solve it with input. And I read a solution from AlexKowalenko, it still needs an input.1.So is it possible to solve it in one table in LiveReport?2.What's wrong with my SQL code since it only returns Base Group?select a.lastname, a.firstname, a.name from kuaf a where a.deleted = 0and a.type = 1 and a.ownerid in (select b.childid from kuafchildren b whereb.childid in (select c.rlid from kuafrightslist)Thank you very much![To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Shirley_Xie_(xie_(Delete)_1422527)

Yes. Exactly. But there are some users which don't have firstname and lastname. I want to add LogIn Name on it. It looks like:Lastname Firstname LongInName BaseGroup OtherGroupsThank you very much!

eLink User

Message from Alex Kowalenko via eLinkThe following is an Oracle query that lists one group per row and indicateswhether it is the base group or not. In order to put multiple row valuesinto one row you would have to write a concatenation function that returns asingle string from the result set of a query.select u.lastname || ', ' || u.firstname || ' (' || u.name || ')' "Name", (Case when u.groupid = g.id Then 'Base Group' Else 'Group' End) "Type", g.name "Group" 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-alex------Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Tuesday, May 08, 2001 13:41To: eLink RecipientSubject: Yes. Exactly.Yes. Exactly.Posted by xie on 05/08/2001 01:36 PMYes. Exactly.But there are some users which don't have firstname and lastname. I want toadd LogIn Name on it. It looks like:Lastname Firstname LongInName BaseGroup OtherGroupsThank you very much![To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Find all users and their all group in one table without inputhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2516830&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Shirley_Xie_(xie_(Delete)_1422527)

I end up with ID Lastname Firstname LogInName Type Name2527 Xie Shirley xie BaseGroup NP-IT team2527 Xie Shirley xie Group Fuel Handling

OJ_Ryan_Meyer_(AICPAkm_(Delete)_1725193)

When I run this I get the message (ORA-00907: missing right parenthesis ).Did I miss something?

CLARIC017_(Delete)_2571572

Ok.. this is Oracle... but does anyone know what the equivalent of this is in SQL Server?... IE.. "connect by prior" doesn't exist in SQL Server.Thanks,Richard D'Addese

User1_Britannia_Airways_EBAL_Support_(britanniause

Dear AlexCan you please comment further on this and let me know whether a switch can be put on this query in order to get a display of users by their names in alphabetical order.In other words I would like break this query into several perhaps taking it from A to F, G - L and etc.The other question I have as to whether you can post a sample concatenation function that worked for you.I would much appreciate your response on this.Regards

David_Beliveau_(sanmin013_-_(deleted))

If anyone has an equivalent to this for MS SQL please post it!Thanks,Dave