Strategy One

Absolute Non-Agg Metric Query Type

Absolute Non-Agg Metric Query Type is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

When a report contains an absolute non-aggregation metric, the pass that gets the non-aggregated data can be performed in a subquery or in a temporary table.

  • Use Temp Table as set in the Fallback Table Type setting: When this option is set, the table creation type follows the option selected in the VLDB property Fallback Table Type. The SQL Engine reads the Fallback Table Type VLDB setting and determines whether to create the intermediate table as a true temporary table or a permanent table.

    • In most cases, the default Fallback Table Type VLDB setting is Temporary table. However, for a few databases, like UDB for 390, this option is set to Permanent table. These databases have their Intermediate Table Type defaulting to True Temporary Table, so you set their Fallback Table Type to Permanent. If you see permanent table creation and you want the absolute non-aggregation metric to use a True Temporary table, set the Fallback Table Type to Temporary table on the report as well.

  • Use subquery (default): With this setting, the engine performs the non-aggregation calculation with a subquery.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Use Sub-query

Copy 
select a11.CLASS_NBR CLASS_NBR,
a12.CLASS_DESC CLASS_DESC,
sum(a11.TOT_SLS_QTY) WJXBFS1
from DSSADMIN.MARKET_CLASS a11, 
DSSADMIN.LOOKUP_CLASS a12
where a11.CLASS_NBR = a12.CLASS_NBR
and (((a11.MARKET_NBR)
in (select s21.MARKET_NBR
from DSSADMIN.LOOKUP_STORE s21
where s21.STORE_NBR in (3, 2, 1)))
and ((a11.MARKET_NBR)
in (select min(c11.MARKET_NBR)
from DSSADMIN.LOOKUP_MARKET c11
where ((c11.MARKET_NBR)
in (select s21.MARKET_NBR
from DSSADMIN.LOOKUP_STORE s21
where s21.STORE_NBR in (3, 2, 1))))))
group by a11.CLASS_NBR,
a12.CLASS_DESC 

Use Temporary Table as Set in the Fallback Table Type Setting

Copy 
create table TPZZOP00 as
select min(c11.MARKET_NBR) WJXBFS1
from DSSADMIN.LOOKUP_MARKET c11
where ((c11.MARKET_NBR)
 in (select s21.MARKET_NBR
 from DSSADMIN.LOOKUP_STORE s21
 where s21.STORE_NBR in (3, 2, 1)))
select a11.CLASS_NBR CLASS_NBR,
 a12.CLASS_DESC CLASS_DESC,
 sum(a11.TOT_SLS_QTY) WJXBFS1
from DSSADMIN.MARKET_CLASS a11, 
 TPZZOP00 pa1, 
 DSSADMIN.LOOKUP_CLASS a12
where a11.MARKET_NBR = pa1.WJXBFS1 and 
 a11.CLASS_NBR = a12.CLASS_NBR
 and ((a11.MARKET_NBR)
 in (select s21.MARKET_NBR
 from DSSADMIN.LOOKUP_STORE s21
 where s21.STORE_NBR in (3, 2, 1)))
group by a11.CLASS_NBR,
 a12.CLASS_DESC