Re: Oracle Case insensitive search:any proven easy way?
I'm on Oracle 10gR1 and Remedy 6.3 and is running into the same requirements. I have a very crude way to search the Summary field using an Active Link. I'm only piloting it right now with our HelpDesk staff and they said it's totally better than nothing. I wish to expand on it since it seems to work. Maybe someone on the list can make some suggestions. I have them type the word to search into the HelpDesk Summary field then use a button to fire an active link (right now I'm actually just using a menu in the WUT). I use a Set Fields and do the following SQL search: SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/ HH24:MI:SS') + (HPD_HELPDESK.CREATE_TIME / ( 60 * 60 * 24 )), 'MM/DD/YY HH:MI AM') as Create_Date, HPD_HELPDESK.CASE_ID_, case when HPD_HELPDESK.STATUS = 0 then 'New' when HPD_HELPDESK.STATUS = 1 then 'Assigned' when HPD_HELPDESK.STATUS = 2 then 'Work in Progress' when HPD_HELPDESK.STATUS = 3 then 'Pending' when HPD_HELPDESK.STATUS = 4 then 'Resolved' when HPD_HELPDESK.STATUS = 5 then 'Closed' end as Status ,'[' || HPD_HELPDESK.SUMMARY || ']' as Summary, HPD_HELPDESK.CASE_ID_ FROM HPD_HELPDESK WHERE UPPER(HPD_HELPDESK.SUMMARY) LIKE UPPER('%$Summary$%') ORDER BY HPD_HELPDESK.CREATE_TIME DESC The key part is the WHERE statement. Since it turns everything to Upper case then does a comparison. If you index the Summary field then the results would be quicker. The Set Fields will come up with a list if it finds multiple items then pushes the Case ID of what is selected into the Case ID field (this is all happening on the same Search form). I then set the Summary field to NULL then perform a run process PERFORM-ACTION-APPLY to press the Search button. Like I said very crude and I wish I can do this within a table with the EXTERNAL() command so it'll be prettier. But hey it's free. I hope someone has done something simular and expanded on it. Good Luck, Steve On Thu, Apr 24, 2008 at 8:15 AM, surya4u <[EMAIL PROTECTED]> wrote: > We have implemented Remedy ITSM 7.1 on Oracle. > Customer needs case insensitive searches!! > is there any way other than using ALTER SESSION query,because there is > proven performance impact. > > Best way is to go for SQL SERVER?? > > anyone know about easy way in oracle to make case insensitive search?anyone > tried in real life?any impact with remedy performance? > -- > View this message in context: > http://www.nabble.com/Oracle-Case-insensitive-search%3Aany-proven-easy-way--tp16851310p16851310.html > Sent from the ARS (Action Request System) mailing list archive at > Nabble.com. > > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Oracle Case insensitive search:any proven easy way?
Full Text Search will also meet your needs. This was reinstated with ARS 7. -Original Message- From: William H. Will Du Chene <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Thu, 24 Apr 2008 1:44 pm Subject: Re: Oracle Case insensitive search:any proven easy way? SQL Server is case insensitive by default. With Oracle, you need to perform a linguistic sort. alter session set NLS_SORT=BINARY_CI; alter session set NLS_COMP=LINGUISTIC; > We have implemented Remedy ITSM 7.1 on Oracle. > Customer needs case insensitive searches!! > is there any way other than using ALTER SESSION query,because there is > proven performance impact. > > Best way is to go for SQL SERVER?? > > anyone know about easy way in oracle to make case insensitive > search?anyone > tried in real life?any impact with remedy performance? > -- > View this message in context: > http://www.nabble.com/Oracle-Case-insensitive-search%3Aany-proven-easy-way--tp16851310p16851310.html > Sent from the ARS (Action Request System) mailing list archive at > Nabble.com. > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- - Will Du Chene - [EMAIL PROTECTED] http://www.myspace.com/wduchene - "...you're an anti-Microsoft zealot..." - Norm Kaiser - ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Oracle Case insensitive search:any proven easy way?
SQL Server is case insensitive by default. With Oracle, you need to perform a linguistic sort. alter session set NLS_SORT=BINARY_CI; alter session set NLS_COMP=LINGUISTIC; > We have implemented Remedy ITSM 7.1 on Oracle. > Customer needs case insensitive searches!! > is there any way other than using ALTER SESSION query,because there is > proven performance impact. > > Best way is to go for SQL SERVER?? > > anyone know about easy way in oracle to make case insensitive > search?anyone > tried in real life?any impact with remedy performance? > -- > View this message in context: > http://www.nabble.com/Oracle-Case-insensitive-search%3Aany-proven-easy-way--tp16851310p16851310.html > Sent from the ARS (Action Request System) mailing list archive at > Nabble.com. > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- - Will Du Chene - [EMAIL PROTECTED] http://www.myspace.com/wduchene - "...you're an anti-Microsoft zealot..." - Norm Kaiser - ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Oracle Case insensitive search:any proven easy way?
Build workflow to set all to upper on submit and search -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of surya4u Sent: Thursday, April 24, 2008 2:16 PM To: arslist@ARSLIST.ORG Subject: Oracle Case insensitive search:any proven easy way? We have implemented Remedy ITSM 7.1 on Oracle. Customer needs case insensitive searches!! is there any way other than using ALTER SESSION query,because there is proven performance impact. Best way is to go for SQL SERVER?? anyone know about easy way in oracle to make case insensitive search?anyone tried in real life?any impact with remedy performance? -- View this message in context: http://www.nabble.com/Oracle-Case-insensitive-search%3Aany-proven-easy-w ay--tp16851310p16851310.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"