This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
Remedy AR System Server
Remedy AR System Server, Remedy IT Service Management, Oracle 18c
Site menu on CTM:People form (also on Asset forms, Product Categories and Resolution Product Categories etc.) did not return any list even though Sites does exist in the system.
The SQL associated with the population of the field is shown below.
SELECT DISTINCT T538.C260000001 FROM T538 WHERE ((((T538.C1000000001 = ' ') OR (' ' = ' ')) AND ((T538.C200000012 = ' ') OR (' ' = ' ')) AND ((T538.C200000007 = ' ') OR (' ' = ' ')) AND (T538.C1000000073 = 0) AND (T538.C1000000081 = 1) AND (T538.C7 = 1))) AND ( ROWNUM <= 20101 ) ORDER BY T538.C260000001 ASC
Oracle version: 18c i.e. 22.214.171.124
AR and ITSM 19.02
Oracle, Bug 27416997 and Case Insensitive queries failing due to NULL is NOT NULL predicate
The "Db-Case-Insensitive" parameter was set to "T".
It was found that if Remedy was being run in Case Insensitive mode, certain use cases would not return expected rows. In this example, "Site" drop down menu on the CTM:People form had returned 72 rows (as expected) when Remedy was in Case Sensitive mode, however NO RECORDS were listed when it was ran in Case Insensitive mode.
It turned out to be hitting a known Oracle Bug 27416997 (check Metalink Doc ID 2390584.1).You will need Oracle credentials to access this Bug, you may reach out to your DBA). The Bug has been resolved in Oracle 19.1.
To address this Site menu issue, we set below parameters (as per above Oracle solution doc) and it resolved the issue:
alter system set "_optimizer_generate_transitive_pred"=FALSE scope=both
BMC Communities Blog reference: