Apostrophe on Multi-select Lookup Value Causes Error

Products

• Clarity PPM SaaS

Issue/Introduction

When selecting multiple values on a multi-select lookup attribute and at least one of those values has apostrophe, the value is successfully getting inserted in the database, but a “System error. Contact system administrator” error is thrown. The app log contains the following:

SQL error code: 907
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00907: missing right parenthesis.

Environment

Release : All Releases

Component : CA PPM STUDIO

Cause

The in statement that is part of the sql query that runs in the background when multiple values are selected uses single quotes to differentiate between values. The apostrophe in one or more of the values then prevents the query from being interpreted correctly. 

Resolution

When creating the dynamic query to be used by a multi-valued attribute, the Hidden Key selected on the Parent Window tab of the lookup properties should be a value that is both unique and one that does not have special characters, particularly an apostrophe. Since the Hidden Key cannot be changed after the lookup is created, a new lookup would need to be used or the apostrophe in the values changed or removed.