For the following example filter
User Tag 2 contains [_]cpm[_]
the corresponding sql used is
SELECT DISTINCT
'1' AS element_type,
cm_computer_system.cs_id AS member_id,
cm_computer_system.cs_key AS instance_id
, cm_computer_system_attr.cs_attr_key AS attr_key
, cm_computer_system_attr.cs_attr_value AS attr_value
FROM cm_computer_system_attr
JOIN cm_computer_system ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id
JOIN cm_computer_system_origin ON cm_computer_system_origin.cs_id = cm_computer_system.cs_id
WHERE cm_computer_system.cs_id IN
( SELECT DISTINCT cm_computer_system_attr.cs_id
FROM cm_computer_system
JOIN cm_computer_system_attr ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id
LEFT JOIN cm_device d ON (cm_computer_system.cs_id = d.cs_id AND d.probe_name = 'controller')
LEFT JOIN cm_nimbus_robot ON d.dev_id = cm_nimbus_robot.dev_id
WHERE (
( cm_nimbus_robot.user_tag_2 LIKE '%[_]cpm[_]%' )
)
)
ORDER BY member_id
Which works fine in SQL but in Oracle returns no rows.
Oracle does not have a default escape character. Instead this needs to be defined in the query. for example:
SELECT DISTINCT
'1' AS element_type,
cm_computer_system.cs_id AS member_id,
cm_computer_system.cs_key AS instance_id
, cm_computer_system_attr.cs_attr_key AS attr_key
, cm_computer_system_attr.cs_attr_value AS attr_value
FROM cm_computer_system_attr
JOIN cm_computer_system ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id
JOIN cm_computer_system_origin ON cm_computer_system_origin.cs_id = cm_computer_system.cs_id
WHERE cm_computer_system.cs_id IN
( SELECT DISTINCT cm_computer_system_attr.cs_id
FROM cm_computer_system
JOIN cm_computer_system_attr ON cm_computer_system_attr.cs_id = cm_computer_system.cs_id
LEFT JOIN cm_device d ON (cm_computer_system.cs_id = d.cs_id AND d.probe_name = 'controller')
LEFT JOIN cm_nimbus_robot ON d.dev_id = cm_nimbus_robot.dev_id
WHERE (
( cm_nimbus_robot.user_tag_2 LIKE '%[_cpm[_%' ESCAPE '[')
)
)
ORDER BY member_id
There is no way to define this in USM and thus the filter fails