if you turn on sql-logging and search for users belong to administrator group you will see a statement like this. SELECT * FROM ( SELECT T31.C1,C101,C536870913,C8,C536870912,C540000000,C540000002 FROM T31 WHERE ((T31.C104 LIKE '1;%') OR (T31.C104 LIKE '% 1;%') OR (T31.C104 LIKE '%;1;%')) ORDER BY 1 ASC ) This makes sense because in earlier versions of arsystem the groupids were separeted by ;<space> and now it's only separated by ; Kind Regards Conny
________________________________ Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von Joe D'Souza Gesendet: Dienstag, 16. Februar 2010 23:27 An: arslist@ARSLIST.ORG Betreff: Re: SQL help needed ** The second statement should be select count(*) from t9 where c104 like '1; %' or c104 like '% 1; %'; However I would rather use the view name of the user form which is user_x instead of t9 because if you are writing code that needs to be migrated across servers, writing SQL statements on the T tables may not be the best idea unless in your environment you are absolutely sure that you have the whole table structures migrated across systems maintaining the identical T numbers for those tables across environments.. So I would rather prefer the statement select count(*) from user_x where group_list like '1; %' or group_list like '% 1; %'; This statement will be good for all environments irrespective of how careful you are to ensure ideal case database migrations between different environments... As far as parsing the group ID's to real group names, if you absolutely have to do it using direct SQL queries why not try writing a stored procedure to do it? It may be a lot easier.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of remedy.help Sent: Tuesday, February 16, 2010 5:09 PM To: arslist@ARSLIST.ORG Subject: Re: SQL help needed ** What i need --> I need to pull out the group names from group list field(c104). Since it contains the group id and not the group names i need to convert it back to group names; Also can we do a select statement for group list field where we say select count(*) from t9 where c104 like '1'; I am on oracle DB and the list i have checked for every other string habits; regards On Tue, Feb 16, 2010 at 4:00 PM, Joe D'Souza <jdso...@shyle.net> wrote: ** Have you tried retrieving the same using a simple select statement using a like clause? I do not see a distinct advantage of using a cursor to retrieve what you are trying to. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of remedy.help Sent: Tuesday, February 16, 2010 4:42 PM To: arslist@ARSLIST.ORG Subject: Re: SQL help needed ** the error points to the Fetch statement Fetch C1 into froup_id,group_name It will be great if somebody has done sql manipulations on the C104 filed of the t9(User Form) On Tue, Feb 16, 2010 at 3:37 PM, Joe D'Souza <jdso...@shyle.net> wrote: ** What is the error that you are getting? Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of amit dw Sent: Tuesday, February 16, 2010 4:20 PM To: arslist@ARSLIST.ORG Subject: SQL help needed ** Hello All, Below is the problem and way I was trying to handle it C_list contains comma separated values of group Id's. this list contains all the group id's associated to a particular login name. C_list= (1,15005,800005,800003,800004,805) This variable will be holding up dynamically value in numbers. For group id numbers in simple sql statement we can get the result corresponding name using the below query, Select c105,c106 from t10 where c106 in (1, 101,102,104). Same query when I tried transforming in a cursor it throws up a compilation error. The error points out to a fetch statement where we try and fetch the data from the cursor for one particular group id. Declare cursor c1 as select c105,c106 from t10 where c106 in(c_list); Begin Open c1; Loop Fetch C1 into g_num,g_name; dbms_output.put_line(g_name); EXIT WHEN C1%NOTFOUND; END LOOP; Close c1; End; Hope it explains the problem I am facing. Any suggestions/Comments or previously implemented code will be helpful for me. _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
