Data Provider Query validation error

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We are trying to run a query on a MUX Reporting Data Provider, but we are getting a validation error:

This query cannot be validated.
Please correct the following items and revalidate.
An exception occurred while trying to update an object instance odf_sql

The query we run is the following:

SELECT string_to_array(i.INVESTMENT_ID , ';')
FROM dwh_inv_investment i

The app-ca.log file shows the following error:

Caused by: com.niku.union.odf.exception.ODFValidationException: The query is invalid. Please check the query syntax and ensure all required parameters are correctly specified.
at com.niku.union.persistence.query.SQLQuery.validateQuery(SQLQuery.java:311)

When we run the same query against the DWH DB directly it works fine. What can cause this error on the Reporting side?

Environment

Clarity 16.3.3

Cause

We do not support array type attributes in data provider queries.

Resolution

The return type of string_to_array is text array and we do not support array type attributes in data provider queries