KB220329: SQL Engine generates 'SELECT' statement only with aliases without column expressions. Report SQL does not contain the table columns specified for the metrics

SUMMARY:
This issue is classified as a defect with Strategy Technical Support. This will hinder users from correctly generating the SQL for the report in Strategy 9.4.1 Hotfix 6. When running a report that contains two count metrics that have a count distinct setting of 'False' and VLDB Sub Query Type properties set to 'Use Temporary Table, falling back to EXISTS (SELECT "....) for correlated subquery', Metrc does not contain a metric expression and results in incorrect SQL.
 
SYMPTOM:
 
SQL Engine in Strategy 9.4.1 Hotfix 6 generates a 'SELECT' statement without column expressions when a report which contains an attribute element filter, Count metrics with 'Distinct' set to false are executed with the Sub Query VLDB Property Type set to 'Use Temporary Table, falling back to EXISTS (SELECT "....) for correlated subquery'. As shown below, only the column alias exists for the metric 'WJXBFS2'.
 

The SQL is as below. The expression for the metric is missing as highlighted. 
select	pa13.COUNTRY_ID  COUNTRY_ID,
	a14.COUNTRY_NAME  CUST_COUNTRY_DESC0,
	pa13.WJXBFS1  WJXBFS1,
	pa13.WJXBFS2  WJXBFS2
from	(select	a12.COUNTRY_ID  COUNTRY_ID,
		count(a12.COUNTRY_ID)  WJXBFS1,
		  WJXBFS2
	from	`CITY_CTR_SLS`	a11
		join	`LU_CALL_CTR`	a12
		  on 	(a11.CALL_CTR_ID = a12.CALL_CTR_ID)
	where	a12.COUNTRY_ID > 0
	group by	a12.COUNTRY_ID
	)	pa13
	join	`LU_COUNTRY`	a14
	  on 	(pa13.COUNTRY_ID = a14.COUNTRY_ID)

• Create two filters as shown below:
  

• Create metrics as shown below using Tutorial data:
  
  

• Set the Count parameter so the 'Distinct' value is set to 'False'.

• Set Sub Query Type VLDB Property for the database instance to 'Use Temporary Table, falling back to EXISTS (SELECT "....) for correlated subquery'.

 
CAUSE:
 
This is a known issue in Strategy Developer 9.4.1 Hotfix 6.
 
ACTION:
 
This issue has been addressed in Strategy 9.4.1 Hotfix 7. Please install Hotfix 7 to take advantage of the fix.
 
The Strategy Internal Reference Number for the issue discussed in this technical note is 978925.