Skip to main content

Appendix B

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

O2C_VTTP - Create a new transformation with the title “Create Table: 02C_VTTP”:

-- Query 1
DROP TABLE IF EXISTS "O2C_VTTP";

-- Query 2
CREATE TABLE "O2C_VTTP" AS (
     SELECT
          "VTTP"."MANDT"
          ,"VTTP"."TKNUM"
          ,"VTTP"."TPNUM"
          ,"VTTP"."VBELN"
          ,"VTTP"."ERDAT"
          ,CAST("VTTP"."ERDAT" AS DATE) AS "TS_ERDAT"
          ,CAST("VTTP"."ERZET" AS DATE) AS "TS_ERZET"
     FROM "VTTP" AS "VTTP"
     WHERE EXISTS(
          SELECT *
          FROM "O2C_VBFA_V" AS "VBFA"
          WHERE 1=1
               AND "VBFA"."MANDT" = "VTTP"."MANDT"
               AND "VBFA"."VBELN" = "VTTP"."VBELN"
     )
);

O2C_VTTK - Create a new transformation with the title “Create Table: O2C_VTTK”:

-- Query 1
DROP TABLE IF EXISTS "O2C_VTTK";

-- Query 2
CREATE TABLE "O2C_VTTK" AS (
     SELECT
          "VTTK".*
          ,"T001W"."NAME1"
          ,"T001W"."LAND1"
          ,"T001W"."PSTLZ"
          ,"T001W"."ORT01"
          ,"T173T"."BEZEI"
          ,CAST("VTTK"."ERDAT" AS DATE) AS "TS_ERDAT"
          ,CAST("VTTK"."ERZET" AS DATE) AS "TS_ERZET"
     FROM "VTTK"
          LEFT JOIN "T173T" ON 1=1
          AND "VTTK"."MANDT"="T173T"."MANDT"
          AND "VTTK"."VSART"="T173T"."VSART"
          AND "T173T"."SPRAS"='E'
          LEFT JOIN "T001W" ON 1=1
          AND "VTTK"."MANDT"="T001W"."MANDT"
          AND "VTTK"."TPLST"="T001W"."WERKS"
          WHERE EXISTS(
     SELECT *
          FROM "O2C_VTTP" AS "VTTP"
          WHERE 1=1
          AND VTTP.MANDT = VTTK.MANDT
          AND VTTP.TKNUM = VTTK.TKNUM
     )
);

O2C_VTSP - Create a new transformation with the title “Create Table: 02C_VTSP”:

-- Query No: 1
DROP TABLE IF EXISTS "O2C_VTSP";

-- Query No: 2
CREATE TABLE "O2C_VTSP" AS (
     SELECT
          "VTSP".*
     FROM "VTSP" AS "VTSP"
     WHERE EXISTS(
          SELECT *
          FROM "O2C_VTTP" AS "VTTP"
          WHERE 1=1
               AND VTSP.TKNUM = VTTP.TKNUM
               AND VTSP.TPNUM = VTTP.TPNUM
     )
   )
;

O2C_VTTS - Create a new transformation with the title “Create Table: O2C_VTTS”:

-- Query No: 1
DROP TABLE IF EXISTS "O2C_V

-- Query No: 2
CREATE TABLE "O2C_VTTS" AS (
     SELECT
          "VTTS".*
          ,"T173T".BEZEI
          ,CAST("VTTS"."ERDAT" AS DATE) AS "TS_ERDAT"
          ,CAST("VTTS"."ERZET" AS DATE) AS "TS_ERZET"
     FROM "VTTS" AS "VTTS"
     LEFT JOIN T173T ON 1=1
          AND T173T.MANDT = VTTS.MANDT
          AND T173T.VSART = VTTS.VSART
          AND T173T.SPRAS = 'E'
     WHERE EXISTS(
          SELECT *
          FROM "O2C_VTSP" AS "VTSP"
          WHERE 1=1
               AND VTSP.MANDT = VTTS.MANDT
               AND VTSP.TKNUM = VTTS.TKNUM
               AND VTSP.TSNUM = VTTS.TSNUM
          )
     )
;