Imprementation Track
Process Connection Basics
Exercise: Extract Data - Create Extraction
TRAINING.EKKO
Suggested solution:
EKKO.AEDAT >= '01.06.2014’
Configure columns:
AEDAT == STRING
Extraction Preview:
AEDAT(1) == 2016-05-02 00:00:00.0
Doesn’t work:
1. EKKO.AEDAT >= '01.06.2014’
2. EKKO.AEDAT >= '2014-06-01 00:00'
3. EKKO.AEDAT >= '2014-06-01 00:00:00.0'
Filter contains a data type mismatch. Column "AEDAT" has data type: "
DATETIME". You supplied an value of type: "STRING". Please adjust your value.
4. AEDAT >= DATETIME('2014-06-01 00:00:00.0')
Error parsing filter: AEDAT >= DATETIME('2014-06-01 00:00:00.0') caused by:
Encountered " <LIST_STRING> "(\\'2014-06-01 00:00:00.0\\') "" at line 1, column 18.
Was expecting one of: <EOF> "AND" ... "OR" ...
5 . AEDAT >= DATE('2014-06-01 00:00:00.0')
Error parsing filter: AEDAT >= DATE('2014-06-01 00:00:00.0') caused by:
Encountered " <LIST_STRING> "(\\'2014-06-01 00:00:00.0\\') "" at line 1, column 18.
Was expecting one of: <EOF> "AND" ... "OR" ...
What works:
6. AEDAT >= DATE('2014-06-01 00:00:00')
Questions:
Why is the data type of the AEDAT column not recognized as String if the
configuration is set to string? (See 1-3)
Why does DATE work here? (see 6)
TRAINING.EKPO
Suggested solution:
Custom join condition
EKPO.MANDT = EKKO.MANDT AND
EKPO.EBELN = EKKO.EBELN
Filter on joined table
EKPO.AEDAT >= '01.06.2014’
Configure columns:
AEDAT == STRING
Doesn’t work:
EKPO.AEDAT >= '01.06.2014'
EKPO.AEDAT >= '01.06.2014 00:00'
Extraction failed!
EKPO.AEDAT >= DATE('2014-06-01 00:00:00')
Extraction preview:
Only Column names visible, the rest is empty
Question:
What’s the right solution here?
Exercise: Validate the Data Extract
Q1: How man table records does the table EKKO contain?
Expected Result:
14067
Suggested Solution:
SELECT COUNT(*) FROM "EKKO"
Actual Result:
70335
What works:
SELECT COUNT(DISTINCT("EBELN")) FROM "EKKO"
14067
Questions:
Shouldn’t these rows be unique. Is this caused by a faulty join?
If I use the suggested solution, then all results for this exercise are wrong!
Create the Activity Table (03:10)
Suggested solution:
DROP TABLE IF EXISTS _CEL_P2P_ACTIVITIES;
CREATE TABLE _CEL_P2P_Activities(
"_CASE_KEY" VARCHAR(50)
,"ACTIVITY_EN" VARCHAR(200)
,"EVENTTIME" TIMESTAMP
,"SORTING" INT
);
Insert First Activity (08:50)
Suggested solution with comments:
/*
Insert Result into Activity Table
*/
INSERT INTO _CEL_P2P_ACTIVITIES("_CASE_KEY","ACTIVITY_EN","EVENTTIME", "SORTING")
/*
Extract relevant infrmation from the specified objects
(_CASE_KEY,EVENTTIME, Activity_EN and SORTING)
*/
SELECT
"EKPO"."MANDT" || "EKPO"."EBELN" || "EKPO"."EBELP" AS "_CASE_KEY"
, 'Create Purchase Requisition Item' AS "ACTIVITY_EN"
, "EBAN"."BADAT" AS "EVENTTIME"
, 10 AS "SORTING"
/*
Specify Objects you want to analyse:
Client(MANDT) and Purchasing Document Number(EBELN)
*/
FROM "EKPO"
JOIN "EKKO" ON
"EKPO"."MANDT" = "EKKO"."MANDT"
AND "EKPO"."EBELN" = "EKKO"."EBELN"
/*
Add extra information:
Purchase Requisition Number(BANFN), Item Number of Purchase Requisition(BNFPO) &
Client(MANDT)
*/
JOIN "EBAN" ON
"EKPO"."MANDT" = "EBAN"."MANDT"
AND "EKPO"."BANFN" = "EBAN"."BANFN"
AND "EKPO"."BNFPO" = "EBAN"."BNFPO"
WHERE "EKKO"."BSTYP" ='F';
Expected result:
136 rows affected
Actual result:
Execution error: ERROR: Column "EVENTTIME" is of type timestamp but expression is
of type varchar
Question:
What’s going on here? Is this due joining problems from earlier?