Query Based Data Provider validates successfully but throws an error on the Preview tab

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When creating or modifying a Query Based Data Provider in Clarity, the query validates successfully without any errors. However, when navigating to the Preview tab or attempting to add columns to the preview, the system throws a syntax error (e.g., indicating an issue with a WHERE clause or a general SQL exception).

Environment

Clarity PPM 16.x

Cause

This issue occurs when the very last line of the SQL query ends with a single-line SQL comment (--).

When the Data Provider executes (such as during a Preview), Clarity wraps the user's query into a parenthesized sub-query to append necessary system code.

Example of Clarity's Wrapper: SELECT [columns] FROM ( [User's Custom Query] ) [Clarity Code]

If the user's custom query ends with a -- comment, the closing parenthesis ) and the trailing Clarity system code are pulled into the single-line comment. Because the system code does not get pushed to a new line, it effectively comments out the end of the wrapper, breaking the SQL statement and resulting in an error.

Resolution

Make sure that there are no comments in the Query, especially at the end of the query.