SYMPTOM:
Freeform SQL reports can be used as "report as filter" for other reports. For more information about using report as filter, refer to:
However when doing so, users notice that derived tables are never used during SQL Generation.
STEPS TO REPRODUCE:
Execute the following in Strategy Tutorial:
1. Create a Freeform SQL with Tutorial. In this example, the report sql is defined as:
select MONTH_ID from LU_MONTH where MONTH_ID = 201105
2. Create another report using this freeform SQL as "report-as-filter."
3. View SQL of the report and check the report SQL:
create table ZZOP00 (
MONTH_ID LONG)
insert into ZZOP00 values ([Analytical Engine Results: MONTH_ID])
select a12.[YEAR_ID] AS YEAR_ID,
sum((a11.[QTY_SOLD] * a11.[UNIT_COST])) AS WJXBFS1,
sum((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] - a11.[DISCOUNT]))) AS Revenue,
avg((a11.[UNIT_PRICE] - a11.[UNIT_COST])) AS WJXBFS2
from [ORDER_DETAIL] a11,
[LU_DAY] a12,
[ZZOP00] pa13
where a11.[ORDER_DATE] = a12.[DAY_DATE] and
a12.[MONTH_ID] = pa13.[MONTH_ID]
group by a12.[YEAR_ID]
drop table ZZOP00
4. Change VLDB Properties > Intermediate table type to Derived tables.
5. Re-generate SQL. Notice that the table type remains as "Permanent table."
CAUSE:
This is working as designed. When Freeform SQL report is used for a filter, derived tables cannot be generated in the report SQL. Some Freeform SQL report designs use syntax that is not valid for derived tables so it is not possible to use derived table logic for any Freeform SQL reports.
WORKAROUND:
Use a regular report for a filter, and then derived tables can be generated:
select a12.[YEAR_ID] AS YEAR_ID,
sum((a11.[QTY_SOLD] * a11.[UNIT_COST])) AS WJXBFS1,
sum((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] - a11.[DISCOUNT]))) AS Revenue,
avg((a11.[UNIT_PRICE] - a11.[UNIT_COST])) AS WJXBFS2
from [ORDER_DETAIL] a11,
[LU_DAY] a12,
(select a11.[MONTH_ID] AS MONTH_ID
from [LU_MONTH] a11
where a11.[MONTH_ID] in (201006)
) pa13
where a11.[ORDER_DATE] = a12.[DAY_DATE] and
a12.[MONTH_ID] = pa13.[MONTH_ID]
group by a12.[YEAR_ID]
