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 ) ) ;