Case Study AP - Set or Remove Payment Block (CASE WHEN)

Hi everyone!
I’m trying to do the final exercise in Data Engineer academy, the Case Study AP.
My problem is with the CASE WHEN statement.
Can you guys help and take a look in my code please?

INSERT INTO _CEL_AP_ACTIVITIES("_CASE_KEY" , “ACTIVITY” , “EVENTTIME” , “SORTING”)
SELECT DISTINCT
“BSEG”.“MANDT” || “BSEG”.“BUKRS” || “BSEG”.“BELNR” || “BSEG”.“GJAHR” || “BSEG”.“BUZEI” AS “_CASE_KEY”,
CASE
WHEN “CDPOS”.“VALUE_NEW” IS NULL THEN ‘Remove Payment Block’
WHEN “CDPOS”.“VALUE_OLD” IS NULL THEN ‘Set Payment Block’ END
AS “ACTIVITY”,
CAST (“CDHDR”.“UDATE” AS DATE) + CAST(“CDHDR”.“UTIME” AS TIME) AS “EVENTTIME”,
CASE
WHEN “CDPOS”.“VALUE_NEW” IS NULL THEN 30
WHEN “CDPOS”.“VALUE_OLD” IS NULL THEN 20 END
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”
JOIN “CDPOS” ON
“CDPOS”.“TABNAME” = ‘BSEG’
AND “CDPOS”.“TABKEY” = “BSEG”.“MANDT” || “BSEG”.“BUKRS” || “BSEG”.“BELNR” || “BSEG”.“GJAHR” || “BSEG”.“BUZEI”
JOIN “CDHDR” ON
“CDHDR”.“MANDANT” = “CDPOS”.“MANDANT”
AND “CDPOS”.“CHANGENR” = “CDHDR”.“CHANGENR”

WHERE BSEG.BSCHL = ‘31’
AND CDPOS.VALUE_NEW IS NULL OR CDPOS.VALUE_OLD IS NULL
AND CDPOS.FNAME = ‘ZLSPR’ ;

As a result, the code is processed without error, but the quantity of the columns affected in the ‘Remove Payment Block’ activity is different than show in the exercise:
1643 (my result) against 1639 (expected result)
Attachment a print of the exercise

Hi Lucas,

I checked you code. The one unusual aspect I saw is that when joining CDPOS and CDHDR on to BSEG you are not using an inner join as is typically done but rather a left join (if you just write JOIN then a left join is used). It’s possible that there 4 cases for which the conditions in the WHERE part of the code is fulfilled without there being corresponding entries in CDPOS and CDHDR tables. So, I would recommend replacing the section:

JOIN “CDPOS” ON

“CDPOS”.“TABNAME” = ‘BSEG’

AND “CDPOS”.“TABKEY” = “BSEG”.“MANDT” || “BSEG”.“BUKRS” || “BSEG”.“BELNR” || “BSEG”.“GJAHR” || “BSEG”.“BUZEI”

JOIN “CDHDR” ON

“CDHDR”.“MANDANT” = “CDPOS”.“MANDANT”

AND “CDPOS”.“CHANGENR” = “CDHDR”.“CHANGENR”

with

INNER JOIN “CDPOS” ON

“CDPOS”.“TABNAME” = ‘BSEG’

AND “CDPOS”.“TABKEY” = “BSEG”.“MANDT” || “BSEG”.“BUKRS” || “BSEG”.“BELNR” || “BSEG”.“GJAHR” || “BSEG”.“BUZEI”

INNER JOIN “CDHDR” ON

“CDHDR”.“MANDANT” = “CDPOS”.“MANDANT”

AND “CDPOS”.“CHANGENR” = “CDHDR”.“CHANGENR”

If this doesn’t sort out the problem, then please write back.

Best wishes,

Calandra

Hi Calandra!
Thanks for your attention and sugestion!
Unfortunately, even with this change the code did not bring the expected result, it kept showing the previous value.
what else can we do to solve this problem?

Thank you very much!
Lucas