KB440840: "Nested SubQuery expressions are not supported" SQL execution error from Hive 2.x in MicroStrategy User icon

SYMPTOM

Report SQL using a correlated subquery is executed against Hive in Strategy 10.x. Depending on the configuration, the following error may be produced:  "Error processing query/statement...Nested SubQuery expressions are not supported."

CAUSE

Hive doesn't support nested subqueries.

FIX

This issue can be resolved by changing the SubQuery Type In VLDB Properties at either the Report level or Database Instance level.
If you encounter the error in Strategy Developer, the fix can be made at the report or project level. If you encounter the error in Web, a Strategy administrator will have to apply the fix at the Report or Database Instance level.

Report Level Fix

• Open Strategy Developer
• Click on Project Source > Navigate to the report > Right Click and select View SQL

• Right Click on Data and select VLDB Properties

• Expand Query Optimizations

• Select Sub Query Type

• Uncheck "Use default inherited value - (DBMS level) and select "Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery

• Click on Save and Close

• In the SQL View, you would notice that the SQL now adheres to the new pattern that we just selected.
• Click on the tiny down arrow next to the pencil icon and select the "Grid View" to see the results with the updated SQL pattern.

DB Instance Level Fix

• Go to Strategy Developer
• Click on Project Source > Administration > Configuration Managers > Database Instances

• Right click the EMRHive DB Instance used for the project source > Select VLDB Properties >

• Select Query Optimizations > Sub Query Type

• Set the Sub Query Type to Use Temporary Table, falling back to LEFT SEMI JOIN for correlated subquery and click on Save and Close.

• Restart the I-Server for the changes to take affect.