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-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
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
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?
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
