Apostrophe on Multi-select Lookup Value Causes Error

book

Article ID: 143175

calendar_today

Updated On:

Products

STARTER PACK-CLARITY PPM 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.

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. 

Environment

Release : All Releases

Component : CA PPM STUDIO

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.