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. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
