Appreciate the Pointer..Will work on that and update with the results; On Tue, Feb 16, 2010 at 4:57 PM, Joe D'Souza <jdso...@shyle.net> wrote:
> ** > The reason that '1%' will throw a lot of results some of which are not > relevant is because by that you are indicating that all records that have > C104 end with 1 to be returned.. > > Instead if you use C104 like '1;%' or C104 like '% 1;%' (notice the space > before 1), you will limit the results to wherever there is a 1; only.. > > I do not have a model code for your other example, but you could check the > SQL that Remedy uses, to convert the string from ID's to name when you pull > up a user record on the user form.. SQL logging will reveal that. They do > not use a stored procedure, but you could use the same SQL logic that is > used to write your own.. > > 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:39 PM > *To:* arslist@ARSLIST.ORG > *Subject:* Re: SQL help needed > > ** > 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 <jdso...@shyle.net> 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: >> 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"
