You pass it the delimiter split(u.GROUP_LIST, ';') By using the Table function you can treat the result as its own table select u.LOGIN_NAME, s.COLUMN_VALUE as Permission_Value from USER_X u, table(split(u.GROUP_LIST, ';')) s Where u.login_name = 'dsouzaj'
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Monday, May 07, 2012 4:57 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy 7.6.04 Removing Permission group So what is this supposed to do? How would this function know it has to delimit at every ; and strip of the string in between? -----Original Message----- From: Grooms, Frederick W Sent: Monday, May 07, 2012 5:04 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Remedy 7.6.04 Removing Permission group In Oracle you have to create the function: CREATE OR REPLACE TYPE SPLIT_TBL as table of varchar2(32767); CREATE OR REPLACE FUNCTION SPLIT (p_list varchar2, p_del varchar2 := ',') return split_tbl pipelined is l_idx pls_integer; l_list varchar2(32767) := p_list; l_value varchar2(32767); begin loop l_idx := instr(l_list,p_del); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(p_del)); else pipe row(l_list); exit; end if; end loop; return; end split; To use it you would have SQL like: select u.LOGIN_NAME, s.COLUMN_VALUE as Permission_Value from USER_X u, table(split(u.GROUP_LIST, ';')) s Where u.login_name = 'dsouzaj' Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Monday, May 07, 2012 3:43 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy 7.6.04 Removing Permission group How would I be able to split: ;20311;20301;20310;20300;482;20214;20218;20219;20212;20217;20501;20500;20504;20008;20010;71001;71000;20004;20005;1102;20034;20052;20026;20041;20025;20024;20023;20029;20028;20060;402;805;812;20013;20014;809;806;20021;20020;20350;1303;20000;1;20216;20222;20002;1302;20032;20003;20007;71002;20012;1000000169;20302;20019;1400; using the split function? I tried (without really checking out its syntax so it could be wrong) SQL> select split(group_list) from user_x where login_name = 'dsouzaj'; select split(group_list) from user_x where login_name = 'dsouzaj' * ERROR at line 1: ORA-00904: "SPLIT": invalid identifier Joe -----Original Message----- From: Grooms, Frederick W Sent: Monday, May 07, 2012 4:14 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Remedy 7.6.04 Removing Permission group Joe, You should be able to split the group_list string into an array of values. What DB are you on? In Oracle you can create a split function and use it as in inline table to join to. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Monday, May 07, 2012 2:42 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy 7.6.04 Removing Permission group I didn't think of the meta update actually.. does it do foundation records such as the people group permissions? I cannot delete from the user as doing so will mean it will take off the permissions from the user form. I do not want that to happen. Those permissions are correct and should be as is. I cannot afford to loose that before creating the csv data load for the PPG form. After that it wont matter.. For eg if user dsouzaj has the following.. SQL> select group_list from user_x where login_name = 'dsouzaj'; ;20311;20301;20310;20300;482;20214;20218;20219;20212;20217;20501;20500;20504;20008;20010;71001;71000;20004;20005;1102;20034;20052;20026;20041;20025;20024;20023;20029;20028;20060;402;805;812;20013;20014;809;806;20021;20020;20350;1303;20000;1;20216;20222;20002;1302;20032;20003;20007;71002;20012;1000000169;20302;20019;1400; However until I added it manually, I had nothing in the PPG form.. I need to generate this data in the PPG form of near 44 records, based on the contents of group_list for dsouzaj. The above example is perfect in fact as there are only 44 qualifying groups that need to be present in PPG for dsouzaj whereas the user form has 50+ groups.. I need to know what are the qualifying groups that need to be present configured for a user in PPG.. Joe _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
