The Strategy SQL Generation Engine uses temporary tables to accomplish multi-pass SQL. In some situations, users may want to execute customized SQL statements against these temporary tables. This can be accomplished using wildcards.
The wildcard available is ??? and !!!. The ??? returns the most recently created temporary table and !!! returns all the column names.
Method 1:
For example, if this wildcard is used in the 'Table Post Statement 1' Very Large Database (VLDB) setting as follows:
The report's SQL will contain the new statement, with each of the temp table names replacing the ???:
create table ZZMD00 (
Year_Id INTEGER,
DOLLARSALES FLOAT)
insert into ZZMD00
select a11.Year_Id Year_Id,
sum(a11.Tot_Dollar_Sales) DOLLARSALES
from YR_CATEGORY_SLS a11
group by a11.Year_Id
alter table ZZMD00 <etc>
create table ZZMD01 (
Year_Id INTEGER,
LASTYEARSDOL FLOAT)
insert into ZZMD01
select a12.Year_Id Year_Id,
sum(a11.Tot_Dollar_Sales) LASTYEARSDOL
from YR_CATEGORY_SLS a11
join LU_YEAR a12
on (a11.Year_Id = (a12.Year_Id - 1))
group by a12.Year_Id
alter table ZZMD01 <etc>
select distinct pa1.Year_Id Year_Id,
pa1.DOLLARSALES DOLLARSALES,
pa2.LASTYEARSDOL LASTYEARSDOL
from ZZMD00 pa1
join ZZMD01 pa2
on (pa1.Year_Id = pa2.Year_Id)
drop table ZZMD00
drop table ZZMD01
Method 2:
There are situations where users may want to dump temp data into separate tables for record keeping. While Method 1 can be useful if user does not care which temp table to use, Method 2 can provide more flexibility by using a combination of both wildcards to not only retrieve the most recently generated temp table (???), but also its column names (!!!). User can also write stored procedures to select exactly which temp table to use by matching the columns returned.
For example, the user has a report that generates query like this:
... create table TZO5JUY4WMD000 ( YEAR_ID_2 INTEGER(4), Revenue DOUBLE) insert into TZO5JUY4WMD000 select a11.YEAR_ID YEAR_ID_2, sum(a11.TOT_DOLLAR_SALES) Revenue from `YR_CATEGORY_SLS` a11 group by a11.YEAR_ID ...
When the report executes, after the Engine inserts data into the temp table, this query will be executed:
insert into Year_Revenue (YEAR_ID_2, Revenue) select YEAR_ID_2, Revenue from TZO5JUY4WMD000
