SYMPTOM:
In Strategy Developer 9.4.1 and 10.x, the Coalesce() function may be used for attribute form expressions to return values from a particular column if the first parameter used for the function returns NULL values. By default the Strategy database.pds file has the function defined as Coalesce(#0, 0), passing "0" if NULLs are found in the first parameter column. This causes issues when running against an Impala database.
STEPS TO REPRODUCE:
1.) Within Strategy Developer, log into the Tutorial project, and change the "Tutorial Data" database instance to use the "Impala 2.x" connector type.
2.) Create a new attribute "A1" with the following attribute form expression: Coalesce(TOT_DOLLAR_SALES, TOT_UNIT_SALES).
3.) Create a new report with attribute "A1" and view SQL. Note that the second column, "TOT_UNIT_SALES", isn't passed for the Coalesce() function. The "0" from the database.pds file is used.
When ran against Impala, the following error message is seen:
"..Error while executing a query in Impala: [HY000] : AnalysisException: No matching function with signature: coalesce(STRING, TINYINT)..."
Per the Cloudera/Impala documentation, multiple parameter values are allowed to be evaluated as part of the NULL checking for Coalesce() function, before
explicitly assigning a value of "NULL".
https://www.cloudera.com/documentation/enterprise/5-4-x/topics/impala_conditional_functions.html
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
Adjust the Coalesce() function within the Impala section of the database.pds file to be (#0, #1) instead of the current value (#0, 0) to take both input parameters.
WARNING:
Manually editing values in the Strategy .pds file incorrectly may cause serious, project-wide problems that may make your project unusable. Since these are user initiated changes they are not covered by any Strategy warranty. Users are strongly encouraged to backup the Strategy .pds file prior to any alteration.
KB439271
