Version 2021

Insert Post Statement

This property is used to insert your custom SQL statements after CREATE and after the first INSERT INTO SELECT statement for explicit temp table creation. There are five settings, numbered 1-5. Each text string entered in Insert Post Statement 1 through Insert Post Statement 4 is executed separately as a single statement. To execute more than 5 statements, insert multiple statement in Insert Post Statement 5, separating each statement with a ";". The SQL Engine then breaks it into individual statements using ";" as the separator and executes the statements separately.

Multiple INSERT INTO SELECT statements to the same table occur in reports involving partition tables and outer joins. The UNION Multiple Inserts VLDB property does not affect this property, but the Table Creation Type property does. The Table Creation Type property is applicable when the Intermediate Table Type VLDB property is set to Permanent or Temporary table.

Levels at Which You Can Set This

Database instance, report, and template

Example

In the following example, the setting values are:

Insert PostStatement1=/* ??? Insert PostStatement1 */

Table Creation Type= Explicit

select a11.PBTNAME PBTNAME
from HARI_STORE_ITEM_PTMAP a11
create table ZZTIS00H601PO000 (
 ITEM_NBR DECIMAL(10, 0), 
 CLASS_NBR DECIMAL(10, 0), 
 STORE_NBR DECIMAL(10, 0), 
 XKYCGT INTEGER, 
 TOTALSALES FLOAT)
insert into ZZTIS00H601PO000
select a11.ITEM_NBR ITEM_NBR,
 a11.CLASS_NBR CLASS_NBR,
 a11.STORE_NBR STORE_NBR,
 0 XKYCGT,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_ITEM_93 a11
group by a11.ITEM_NBR,
 a11.CLASS_NBR,
 a11.STORE_NBR
insert into ZZTIS00H601PO000
select a11.ITEM_NBR ITEM_NBR,
 a11.CLASS_NBR CLASS_NBR,
 a11.STORE_NBR STORE_NBR,
 1 XKYCGT,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_ITEM_94 a11
group by a11.ITEM_NBR,
 a11.CLASS_NBR,
 a11.STORE_NBR
/* ZZTIS00H601PO000 Insert PostStatement1 */
select pa1.ITEM_NBR ITEM_NBR,
 pa1.CLASS_NBR CLASS_NBR,
 max(a11.ITEM_DESC) ITEM_DESC,
 max(a11.CLASS_DESC) CLASS_DESC,
 pa1.STORE_NBR STORE_NBR,
 max(a12.STORE_DESC) STORE_DESC,
 sum(pa1.TOTALSALES) TOTALSALES
from ZZTIS00H601PO000 pa1
 join HARI_LOOKUP_ITEM a11
 on (pa1.CLASS_NBR = a11.CLASS_NBR and 
 pa1.ITEM_NBR = a11.ITEM_NBR)
 join HARI_LOOKUP_STORE a12
 on (pa1.STORE_NBR = a12.STORE_NBR)
group by pa1.ITEM_NBR,
 pa1.CLASS_NBR,
 pa1.STORE_NBR

Table Creation Type= Implicit

select a11.PBTNAME PBTNAME
from HARI_STORE_ITEM_PTMAP a11
select a11.ITEM_NBR ITEM_NBR,
 a11.CLASS_NBR CLASS_NBR,
 a11.STORE_NBR STORE_NBR,
 0 XKYCGT,
 sum(a11.TOT_SLS_DLR) TOTALSALES
into ZZTIS00H60BPO000
from HARI_STORE_ITEM_93 a11
group by a11.ITEM_NBR,
 a11.CLASS_NBR,
 a11.STORE_NBR
insert into ZZTIS00H60BPO000
select a11.ITEM_NBR ITEM_NBR,
 a11.CLASS_NBR CLASS_NBR,
 a11.STORE_NBR STORE_NBR,
 1 XKYCGT,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_ITEM_94 a11
group by a11.ITEM_NBR,
 a11.CLASS_NBR,
 a11.STORE_NBR
select pa1.ITEM_NBR ITEM_NBR,
 pa1.CLASS_NBR CLASS_NBR,
 max(a11.ITEM_DESC) ITEM_DESC,
 max(a11.CLASS_DESC) CLASS_DESC,
 pa1.STORE_NBR STORE_NBR,
 max(a12.STORE_DESC) STORE_DESC,
 sum(pa1.TOTALSALES) TOTALSALES
from ZTIS00H60BPO000 pa1
 join HARI_LOOKUP_ITEM a11
 on (pa1.CLASS_NBR = a11.CLASS_NBR and 
 pa1.ITEM_NBR = a11.ITEM_NBR)
 join HARI_LOOKUP_STORE a12
 on (pa1.STORE_NBR = a12.STORE_NBR)
group by pa1.ITEM_NBR,
 pa1.CLASS_NBR,
 pa1.STORE_NBR