SYMPTOM:
In Strategy 9.x-10.x, when adding a Freeform SQL (FFSQL) report to the base report as a filter, an incorrect SQL join is generated and the results are incorrect.
The FFSQL is defined as “Select YEAR_ID from LU_DAY cal_dt = date -1”, and the report is quite simple involving only two attributes 'Year' and 'Month' on the template.
Without FFSQL in the report, the SQL is performing join based on MONTH_ID as shown below:
select a11.MONTH_ID MONTH_ID,
max(a12.MONTH_DESC) MONTH_DESC,
a11.DATE_ID DATE_ID
from LU_DAY a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
group by a11.MONTH_ID,
a11.DATE_ID
With FFSQL in the report, the SQL is performing join based on Quarter_ID as shown below:
Select YEAR_ID from LU_DAY where cal_dt = date -1
create multiset volatile table ZZOP00, no fallback, no log(
YEAR_ID INTEGER)
primary index (YEAR_ID) on commit preserve rows
insert into ZZOP00 values ()
select a14.MONTH_ID MONTH_ID,
max(a14.MONTH_DESC) MONTH_DESC,
a16.DATE_ID DATE_ID
from ZZOP00 pa11
join LU_DAY a16
on (pa11.YEAR_ID = a16.YEAR_ID)
join LU_MTH a14
on (a16.QUARTER_ID = a14.QUARTER_ID)
group by a14.MONTH_ID,
a16.DATE_ID
CAUSE:
This is a known issue with Strategy 9.x
WORKAROUND:
At report level set VLDB property Joins >”From Clause Order” to use “Move MQ table in normal From clause order to the last (for RedBrick” as shown below:
