Case insensitive search in detail_chg justification field

book

Article ID: 209911

calendar_today

Updated On:

Products

CA Service Desk Manager CA Service Management - Service Desk Manager

Issue/Introduction

When using the detail_chg form filter, JUSTIFICATION field searching is not case insensitive.

The DESCRIPTION and SUMMARY fields are insensitive.

What is the difference between them?

Environment

CA Service Management 17.3

When the database is ORACLE and case INSENSITIVE.

Resolution

It is expected behavior.

The field JUSTIFICATION is NCLOB and DESCRIPTION also. Unfortunately, the NLS_SORT function does not support NCLOB data directly. On the other hand, when searching for DESCRIPTION, actually, the field DESC_SEARCH is used that is NVARCHAR2, and NLS_SORT supports it.

See the query when searching for DESCRIPTION:

SELECT chg.open_date, chg.sched_start_date, chg.sched_end_date, chg.id, chg.risk, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", chg.chg_ref_num FROM (chg LEFT JOIN attached_sla ON chg.id = attached_sla.mapped_chg LEFT JOIN usp_target_time ON chg.id = usp_target_time.mapped_chg) WHERE chg.active_flag = 1 AND ( chg.desc_search = N'testing' ) GROUP BY chg.open_date, chg.sched_start_date, chg.sched_end_date, chg.id, chg.risk, chg.chg_ref_num ORDER BY chg.open_date DESC NULLS LAST

CHG table:

('ID','NUMBER','No',null,1,null);
('PERSID','NVARCHAR2(30 CHAR)','Yes',null,2,null);
('CHG_REF_NUM','NVARCHAR2(30 CHAR)','No',null,3,null);
('SUMMARY','NVARCHAR2(240 CHAR)','Yes',null,4,null);
('DESCRIPTION','NCLOB','Yes',null,5,null);
('STATUS','NVARCHAR2(12 CHAR)','Yes',null,6,null);
('ACTIVE_FLAG','NUMBER','No',null,7,null);
('START_DATE','NUMBER','Yes',null,8,null);
('OPEN_DATE','NUMBER','Yes',null,9,null);
('LAST_MOD_DT','NUMBER','Yes',null,10,null);
('LAST_MOD_BY','RAW','Yes',null,11,null);
('CLOSE_DATE','NUMBER','Yes',null,12,null);
('RESOLVE_DATE','NUMBER','Yes',null,13,null);
('ROOTCAUSE','NUMBER','Yes',null,14,null);
('EST_TOTAL_TIME','NUMBER','Yes',null,15,null);
('ACTUAL_TOTAL_TIME','NUMBER','Yes',null,16,null);
('LOG_AGENT','RAW','No',null,17,null);
('ASSIGNEE','RAW','Yes',null,18,null);
('ORGANIZATION','RAW','Yes',null,19,null);
('GROUP_ID','RAW','Yes',null,20,null);
('AFFECTED_CONTACT','RAW','No',null,21,null);
('REQUESTOR','RAW','No',null,22,null);
('CATEGORY','NVARCHAR2(12 CHAR)','Yes',null,23,null);
('PRIORITY','NUMBER','No',null,24,null);
('NEED_BY','NUMBER','Yes',null,25,null);
('EST_COMP_DATE','NUMBER','Yes',null,26,null);
('ACTUAL_COMP_DATE','NUMBER','Yes',null,27,null);
('EST_COST','NUMBER','Yes',null,28,null);
('ACTUAL_COST','NUMBER','Yes',null,29,null);
('JUSTIFICATION','NCLOB','Yes',null,30,null);
('BACKOUT_PLAN','NCLOB','Yes',null,31,null);
('IMPACT','NUMBER','Yes',null,32,null);
('PARENT','NUMBER','Yes',null,33,null);
('EFFORT','NVARCHAR2(2000 CHAR)','Yes',null,34,null);
('SUPPORT_LEV','NVARCHAR2(30 CHAR)','Yes',null,35,null);
('TEMPLATE_NAME','NVARCHAR2(30 CHAR)','Yes',null,36,null);
('SLA_VIOLATION','NUMBER','Yes',null,37,null);
('PREDICTED_SLA_VIOL','NUMBER','Yes',null,38,null);
('MACRO_PREDICT_VIOL','NUMBER','Yes',null,39,null);
('CREATED_VIA','NUMBER','Yes',null,40,null);
('CALL_BACK_DATE','NUMBER','Yes',null,41,null);
('CALL_BACK_FLAG','NUMBER','Yes',null,42,null);
('STRING1','NVARCHAR2(40 CHAR)','Yes',null,43,null);
('STRING2','NVARCHAR2(40 CHAR)','Yes',null,44,null);
('STRING3','NVARCHAR2(40 CHAR)','Yes',null,45,null);
('STRING4','NVARCHAR2(40 CHAR)','Yes',null,46,null);
('STRING5','NVARCHAR2(40 CHAR)','Yes',null,47,null);
('STRING6','NVARCHAR2(40 CHAR)','Yes',null,48,null);
('SERVICE_DATE','NUMBER','Yes',null,49,null);
('SERVICE_NUM','NVARCHAR2(30 CHAR)','Yes',null,50,null);
('PRODUCT','NUMBER','Yes',null,51,null);
('ACTIONS','NVARCHAR2(750 CHAR)','Yes',null,52,null);
('TYPE_OF_CONTACT','NUMBER','Yes',null,53,null);
('REPORTING_METHOD','NUMBER','Yes',null,54,null);
('PERSON_CONTACTING','NUMBER','Yes',null,55,null);
('FLAG1','NUMBER','Yes',null,56,null);
('FLAG2','NUMBER','Yes',null,57,null);
('FLAG3','NUMBER','Yes',null,58,null);
('FLAG4','NUMBER','Yes',null,59,null);
('FLAG5','NUMBER','Yes',null,60,null);
('FLAG6','NUMBER','Yes',null,61,null);
('USER1','NVARCHAR2(100 CHAR)','Yes',null,62,null);
('USER2','NVARCHAR2(100 CHAR)','Yes',null,63,null);
('USER3','NVARCHAR2(100 CHAR)','Yes',null,64,null);
('CAWF_PROCID','NVARCHAR2(40 CHAR)','Yes',null,65,null);
('PROJECT','RAW','Yes',null,66,null);
('DESC_SEARCH','NVARCHAR2(2000 CHAR)','Yes',null,67,null);
('TENANT','RAW','Yes',null,68,null);
('CHGTYPE','NUMBER','Yes',null,69,null);
('SCHED_START_DATE','NUMBER','Yes',null,70,null);
('SCHED_END_DATE','NUMBER','Yes',null,71,null);
('SCHED_DURATION','NUMBER','Yes',null,72,null);
('ACTUAL_START_DATE','NUMBER','Yes',null,73,null);
('ACTUAL_END_DATE','NUMBER','Yes',null,74,null);
('BUSINESS_CASE','NCLOB','Yes',null,75,null);
('CAB','RAW','Yes',null,76,null);
('CAB_APPROVAL','NUMBER','Yes',null,77,null);
('CLOSURE_CODE','NUMBER','Yes',null,78,null);
('RISK','NUMBER','Yes',null,79,null);
('EXTERNAL_SYSTEM_TICKET','NCLOB','Yes',null,80,null);
('ORIG_USER_ADMIN_ORG','RAW','Yes',null,81,null);
('ORIG_USER_COST_CENTER','NUMBER','Yes',null,82,null);
('ORIG_USER_DEPT','NUMBER','Yes',null,83,null);
('ORIG_USER_ORGANIZATION','RAW','Yes',null,84,null);
('REQUESTED_BY','RAW','Yes',null,85,null);
('TARGET_CLOSED_COUNT','NUMBER','Yes',null,86,null);
('TARGET_CLOSED_LAST','NUMBER','Yes',null,87,null);
('TARGET_HOLD_COUNT','NUMBER','Yes',null,88,null);
('TARGET_HOLD_LAST','NUMBER','Yes',null,89,null);
('TARGET_RESOLVED_COUNT','NUMBER','Yes',null,90,null);
('TARGET_RESOLVED_LAST','NUMBER','Yes',null,91,null);
('TARGET_START_LAST','NUMBER','Yes',null,92,null);
('CAEXTWF_INSTANCE_ID','NUMBER','Yes',null,93,null);
('HAS_CI','NUMBER','Yes',null,94,null);
('HAS_CI_PLANNED_CHANGE','NUMBER','Yes',null,95,null);
('USM_REQUEST_ID','NUMBER','Yes',null,96,null);
('USM_REQUEST_ITEMID','NUMBER','Yes',null,97,null);
('CURRENT_WRKSHFT','NVARCHAR2(40 CHAR)','Yes',null,98,null);

Attachments