Joe, Thanks for the response but the query on C104 doesnt catches it and if we give like '1%' it throws up lot of results.
I was trying to write stored procedure for getting the names, but there also i am facing issues because of the variable containing mutliple values deliminated by ';' it will be great if you have something written on it and i can use it as model code ... On Tue, Feb 16, 2010 at 4:27 PM, Joe D'Souza <[email protected]> wrote: > ** > 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: > [email protected]]*on Behalf Of *remedy.help > *Sent:* Tuesday, February 16, 2010 5:09 PM > *To:* [email protected] > *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 <[email protected]> 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: >> [email protected]]*on Behalf Of *remedy.help >> *Sent:* Tuesday, February 16, 2010 4:42 PM >> *To:* [email protected] >> *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 <[email protected]> wrote: >> >>> ** >>> What is the error that you are getting? >>> >>> Joe >>> >>> -----Original Message----- >>> *From:* Action Request System discussion list(ARSList) [mailto: >>> [email protected]]*on Behalf Of *amit dw >>> *Sent:* Tuesday, February 16, 2010 4:20 PM >>> *To:* [email protected] >>> *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: [email protected] ARSlist: "Where the Answers > Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
