Table Pre Statement

The Table Pre Statement property is used to insert custom SQL statements before the CREATE TABLE statement. There are five settings, numbered 1-5. Each text string entered in Table Pre Statement 1 through Table Pre Statement 4 is executed separately as a single statement. To execute more than 5 statements, insert multiple statements in Table Pre Statement 5, separating each statement with a ";". The SQL Engine then breaks them into individual statements using ";" as the separator and executes the statements separately. This property is applicable when the Intermediate Table Type VLDB property is set to Permanent or Temporary table or Views. The custom SQL is applied to every intermediate table or view.

Levels at Which You Can Set This

Database instance, report, and template

Example

In the following example, the setting values are:

Table PreStatement1=/* ??? Table

PreStatement1 */

/*Table PreStatement 1*/

create table ZZTIS00H63RMQ000 (

 DEPARTMENT_NBR DECIMAL(10, 0), 

 STORE_NBR DECIMAL(10, 0))

insert into ZZTIS00H63RMQ000

select a11.DEPARTMENT_NBR DEPARTMENT_NBR,

 a11.STORE_NBR STORE_NBR

from HARI_STORE_DEPARTMENT a11

group by a11.DEPARTMENT_NBR,

 a11.STORE_NBR

having sum(a11.TOT_SLS_DLR) > 100000

select a11.DEPARTMENT_NBR DEPARTMENT_NBR,

 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,

 a11.STORE_NBR STORE_NBR,

 max(a13.STORE_DESC) STORE_DESC,

 sum(a11.TOT_SLS_DLR) TOTALSALES

from HARI_STORE_DEPARTMENT a11

 join ZZTIS00H63RMQ000 pa1

 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 

 a11.STORE_NBR = pa1.STORE_NBR)

 join HARI_LOOKUP_DEPARTMENT a12

 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)

 join HARI_LOOKUP_STORE a13

 on (a11.STORE_NBR = a13.STORE_NBR)

group by a11.DEPARTMENT_NBR,

 a11.STORE_NBR