CASE WHEN (Case Study AP) Data Engineer Training track

Hi,
I’m currently trying to solve the “Accounts Payable Case Study” and have now Problems to set up the “Due date expired” activity.
Those are the demands:
Activity name: ‘Due Date Expired’
Timestamp: BSEG.ZFBDT + (BSEG.ZBD1T or BSEG.ZBD2T or BSEG.ZBD3T)
Use a CASE WHEN statement: If BSEG.ZBD3T > 0 add BSEG.ZBD3T to the base date BSEG.ZFBDT.
Else, if BSEG.ZBD2T > 0, add BSEG.ZBD2T to the base date BSEG.ZFBDT.
Else, if BSEG.ZBD1T > 0, add BSEG.ZBD1T to the base date BSEG.ZFBDT.
If BSEG.ZBD1T, BSEG.ZBD2T and BSEG.ZBD3T are 0, use BSEG.ZFBDT.

This is my Code:
INSERT INTO _CEL_AP_ACTIVITIES("_CASE_KEY" , “ACTIVITY_EN” , “EVENTTIME” , “SORTING”)
SELECT
“BSEG”.“MANDT” || “BSEG”.“BUKRS” || “BSEG”.“BELNR” || “BSEG”.“GJAHR” || “BSEG”.“BUZEI” AS “_CASE_KEY”
,‘Due Date Expired’ AS “ACTIVITY_EN”
, CASE WHEN “BSEG”.“ZBD3T” > 0 THEN (“BSEG”.“ZBD3T” + “BSEG”.“ZFBDT”) AS “EVENTTIME”
WHEN “BSEG”.“ZBD2T” > 0 THEN (“BSEG”.“ZBD2T” + “BSEG”.“ZFBDT”) AS “EVENTTIME”
WHEN “BSEG”.“ZBD1T” > 0 THEN (“BSEG”.“ZBD1T” + “BSEG”.“ZFBDT”) AS “EVENTTIME”
ELSE “BSEG”.“ZFBDT” AS “EVENTTIME” END
,40 AS “SORTING”
FROM “BSEG”
JOIN “BKPF” ON
“BSEG”.“MANDT” = “BKPF”.“MANDT”
AND “BSEG”.“BUKRS” = “BKPF”.“BUKRS”
AND “BSEG”.“BELNR” = “BKPF”.“BELNR”
AND “BSEG”.“GJAHR” = “BKPF”.“GJAHR”
WHERE “BSEG”.“BSCHL” = ‘31’
AND “BSEG”.“ZFBDT” IS NOT NULL;

I always get following error message:
Execution error: ERROR: Syntax error at or near “AS”

I’m relatively new to Celonis and SQL, so I don’t know where the error exactly lies, and the Solution Video in this Course is currently under maintenance, so maybe someone here could help me fix this problem.

Thanks.
Kind regards
Max

Hi Maximus,

Welcome to the Celonis Community :slight_smile:

I checked you code, everything look great apart from “Eventtime”. You wrote:

Here I can see two sources of errors, firstly you include “AS “EVENTTIME”” in every condition, Its actually supposed to go at the end of the case when statement (after “END”). The case when statement determines the content of the column for each individual row while “AS “EVENTTIME”” gives the entire column its name (or in the SQL lingo ‘alias’). The second mistake you are making is that you are adding columns containing numbers (“ZBD3T”,” ZBD2T”,” ZBD1T”) to a date column (“ZFBDT”), which may convert the result into a number, its not always strictly necessary but it’s generally safer to explicitly convert the result back into a Date. This type conversion can be done using the function CAST. The default time is 00:00:00 but as due date deadline usually describes the last possible day rather than the day on which its officially late it would be good to set the time to ‘23:59:59’.

The syntax would then look like this:

, CAST( CASE

WHEN „BSEG”.“ZBD3T” > 0 THEN (“BSEG”.“ZBD3T” + “BSEG”.“ZFBDT”)

WHEN “BSEG”.“ZBD2T” > 0 THEN (“BSEG”.“ZBD2T” + “BSEG”.“ZFBDT”)

WHEN “BSEG”.“ZBD1T” > 0 THEN (“BSEG”.“ZBD1T” + “BSEG”.“ZFBDT”)

ELSE “BSEG”.“ZFBDT” END AS DATE) + CAST(‘23:59:59’ AS TIME) AS “EVENTTIME”

I hope I have been able to help you solve your problem and to gain insights that will help you in your future work with Celonis. I wish you all the best for your current training and all future work with Celonis. Feel free to make another post if you have further questions.

Best wishes,
Calandra

Thank you Calandra. Everything works just fine now.

1 Like