MicroStrategy ONE

Insert Mid Statement

The Insert Mid Statement property is used to insert your own custom SQL strings between the first INSERT INTO SELECT statement and subsequent INSERT INTO SELECT statements inserting data into the same table. There are five settings in total, numbered 1-5. Each text string entered in Insert Mid Statement 1 through Insert Mid Statement 4 is executed separately as a single statement. To execute more than 5 statements, you can put multiple statements in Insert Mid 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 affects this property. If the UNION Multiple Inserts VLDB property is set to Use Union, there is only one insert into the intermediate table. This setting 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

Examples

In the following example, the setting values are:

Insert MidStatement1=/* ??? Insert MidStatement1 */

UNION Multiple Inserts = Do Not Use UNION 

select a11.PBTNAME PBTNAME
from HARI_STORE_ITEM_PTMAP a11
create table ZZTIS00H5YAPO000 (
 ITEM_NBR DECIMAL(10, 0), 
 CLASS_NBR DECIMAL(10, 0), 
 STORE_NBR DECIMAL(10, 0), 
 XKYCGT INTEGER, 
 TOTALSALES FLOAT)
insert into ZZTIS00H5YAPO000
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
/* ZZTIS00H5YAPO000 Insert MidStatement1 */
insert into ZZTIS00H5YAPO000
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 ZTIS00H5YAPO000 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

UNION Multiple Inserts = Use UNION 

select a11.PBTNAME PBTNAME
from HARI_STORE_ITEM_PTMAP a11
create table ZZTIS00H5YEPO000 (
 ITEM_NBR DECIMAL(10, 0), 
 CLASS_NBR DECIMAL(10, 0), 
 STORE_NBR DECIMAL(10, 0), 
 XKYCGT INTEGER, 
 TOTALSALES FLOAT)
insert into ZZTIS00H5YEPO000
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
union all
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 ZZTIS00H5YEPO000 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