Thanks Conny, This works great as a query
regards On Wed, Feb 17, 2010 at 1:46 AM, Conny Martin <conny.mar...@t-systems.com>wrote: > ** > 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"_ > _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"
