Customer Hierarchy levels

Dear Celonis Community,

I have a tricky statement to built and could not find a solution yet - maybe one of you have a nice idea! :slight_smile:

In our company we have so-called “local customer hierarchy” which is used on the one hand for grouping of customers and have settings on higher level than customer number. On the other hand used for reporting on different levels. This hierarchy is our “a-level-hierarchy” and it is usually used by the local affiliates. Technically the assignment can be found in entries from KNVH, where KNVH-HITYP = A.

When reporting in headquarters “a-level-hierarchy”-numbers are grouped into “f-level-hierarchy” (also called GCN=GlobalCustomerNumber) and reporting is based on f-level. Technically the assignment can be found in entries from KNVH, where KNVH-HITYP = F.

Basically as not always the lowest level of a-level-hierarchy is assigned to the lowest level of f-level-hierarchy, I have to check every a-level-value against the lowest level of f-level-hierarchy.

I built a statment that will give me the result of this screenshot (VBELN and KUNNR have the same values for both result lines):


The relevant condition from a to f-level-hierarchy is:
AND KNVH_GCN.KUNNR in (KNVH_1A.HKUNNR,
KNVH_1B.HKUNNR,KNVH_1C.HKUNNR,KNVH_1D.HKUNNR,
KNVH_1E.HKUNNR,KNVH_1F.HKUNNR,KNVH_1G.HKUNNR)

In the end I only need the match of the join, that was from the lowest a-level-hierarchy match with the GCN_AHierarchy level in the screenshot - otherwise (as this is part of my VBAK-View for Celonis i have 2 entries for 1 order in VBAK). In this case would be the match of KUNNR_1B column. There is also a match on higher level (KUNNR_1C) but this i do not need, if a lower level found a match.

Do you have any idea on how to check? I am free to explain further, as this is really a heavy topic :slight_smile:
Thank you in advance for your hints!!

BR,
Marcel

Hi Marcel,

I’m sorry about the late reply. After rereading you post many times, I think I may have understood what your trying to achieve (please correct me if I’m wrong):

You want to create a join between the tables so that the value of “GCN_AHierarchy” has to match one of KNVH_1A.HKUNNR, KNVH_1B.HKUNNR, KNVH_1C.HKUNNR ,KNVH_1D.HKUNNR, KNVH_1E.HKUNNR, KNVH_1F.HKUNNR or KNVH_1G.HKUNNR. But you only want these to be tested one after the other, in alphabetical order, so that if you have a row where KNVH_1A.HKUNNR and KNVH_1C.HKUNNR both match, the second match will be ignored as KNVH_1A.HKUNNR has already be found to match.

To do this you can just use a CASE WHEN statement as it evaluates the conditions in order it stops as soon as it finds a TRUE condition and doesn’t consider whether any other conditions might also be fulfilled.

Try this condition in your WHERE statement:

AND (CASE

WHEN KNVH_1A.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1A.HKUNNR

WHEN KNVH_1B.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1B.HKUNNR

WHEN KNVH_1C.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1C.HKUNNR

….

WHEN KNVH_1G.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1G.HKUNNR) = KNVH_GCN.KUNNR

Does this archive the desired result? Have I misunderstood your question?

Best wishes,

Calandra

Hello Calandra,

thank you soooo much for checking my issue and taking the time to read it through even a few times. I know it is very complicated. I tried to use your approach in the ON Clause of the join and also in the where clause of the statement. Neither of them worked unfortunately.

I will share the whole code with you just to give you the whole context:

select vbeln,vkorg,vtweg,kunnr,KUNNR_1A,KUNNR_1B,KUNNR_1C,KUNNR_1D,KUNNR_1E,KUNNR_1F,KUNNR_1G,“GCN_AHierarchy”,GCN from (
SELECT
DISTINCT VBAK.MANDT,
VBAK.VBELN,
VBAK.ERDAT,
VBAK.ERZET,
–VBAK.ERNAM,
VBAK.BNDDT,
VBAK.AUDAT,
VBAK.VBTYP,
(CASE WHEN VBAK.AUART = ‘TA’
THEN ‘OR’ WHEN VBAK.AUART = ‘KL’
THEN ‘FD’
ELSE VBAK.AUART
END) AS “AUART”,
VBAK.LIFSK,
VBAK.NETWR,
VBAK.WAERK,
VBAK.VKORG,
VBAK.VTWEG,
VBAK.BSARK,
VBAK.BNAME,
VBAK.KUNNR,
SHIPTO.KUNNR AS SHIPTO_KUNNR,
SHIPTO.NAME1 AS SHIPTO_NAME1,
VBAK.KVGR1,
VBAK.BUKRS_VF,
VBAK.KKBER,
VBAK.ANGDT,
VBAK.VGBEL,
CAST(VBAK.ERDAT AS DATE) AS TS_ERDAT,
CAST(VBAK.ANGDT AS DATE) AS TS_ANGDT,
CAST(VBAK.HB_RESDATE AS DATE) AS TS_HB_RESDATE,
CAST(VBAK.AEDAT AS DATE) AS TS_AEDAT,
CAST(VBAK.GWLDT AS DATE) AS TS_GWLDT,
CAST(VBAK.DAT_FZAU AS DATE) AS TS_DAT_FZAU,
CAST(VBAK.AUDAT AS DATE) AS TS_AUDAT,
CAST(VBAK.FMBDAT AS DATE) AS TS_FMBDAT,
CAST(VBAK.ABHOD AS DATE) AS TS_ABHOD,
CAST(VBAK.BSTDK AS DATE) AS TS_BSTDK,
CAST(VBAK.MAHDT AS DATE) AS TS_MAHDT,
CAST(VBAK.CMNUP AS DATE) AS TS_CMNUP,
CAST(VBAK.HB_EXPDATE AS DATE) AS TS_HB_EXPDATE,
CAST(VBAK.GUEBG AS DATE) AS TS_GUEBG,
CAST(VBAK.GUEEN AS DATE) AS TS_GUEEN,
CAST(VBAK.CMNGV AS DATE) AS TS_CMNGV,
CAST(VBAK.BNDDT AS DATE) AS TS_BNDDT,
CAST(VBAK.CMFRE AS DATE) AS TS_CMFRE,
CAST(VBAK.VDATU AS DATE) AS TS_VDATU,
CAST(VBAK.ABHOB AS TIME) AS TS_ABHOB,
CAST(VBAK.ERZET AS TIME) AS TS_ERZET,
CAST(VBAK.ABHOV AS TIME) AS TS_ABHOV,
CAST(VBAK.VZEIT AS TIME) AS TS_VZEIT ,
IFNULL(DD07T_VBTYP.DDTEXT,
‘’) AS VBTYP_TEXT ,
IFNULL(TVAKT.BEZEI,
‘’) AS AUART_TEXT ,
IFNULL(DD07T_KLIMP.DDTEXT,
‘’) AS KLIMP_TEXT ,
IFNULL(TVAUT.BEZEI,
‘’) AS AUGRU_TEXT ,
IFNULL(TVLST.VTEXT,
‘’) AS LIFSP_TEXT ,
IFNULL(TVFST.VTEXT,
‘’) AS FAKSK_TEXT ,
IFNULL(TVKOT.VTEXT,
‘’) AS VKORG_TEXT ,
IFNULL(TVTWT.VTEXT,
‘’) AS VTWEG_TEXT ,
IFNULL(TSPAT.VTEXT,
‘’) AS SPART_TEXT ,
IFNULL(TVGRT.BEZEI,
‘’) AS VKGRP_TEXT ,
IFNULL(TVKBT.BEZEI,
‘’) AS VKBUR_TEXT ,
IFNULL(TVSBT.VTEXT,
‘’) AS VSBED_TEXT ,
IFNULL(T014T.KKBTX,
‘’) AS KKBER_TEXT ,
IFNULL(T024B.STEXT,
‘’) AS SBGRP_TEXT ,
IFNULL(T001.BUTXT,
‘’) AS BUKRS_TEXT ,
CASE WHEN VBAK.BSARK = ‘SCHR’
THEN ‘Manual Entry’ – WHEN VBAK.BSARK = ‘SCHR’ AND USR02.USTYP = ‘A’ THEN ‘Manual Entry (manual)’
WHEN VBAK.BSARK = ‘SPS’
THEN ‘Service Provider’ WHEN VBAK.BSARK = ‘WEB’
THEN ‘Web’ WHEN VBAK.BSARK = ‘PHOE’
THEN ‘CRM’ WHEN VBAK.BSARK = ‘DFUE’
THEN ‘EDI’ WHEN VBAK.BSARK = ‘EXE’
AND USR02.USTYP = ‘A’
THEN ‘Excel (manual)’ WHEN VBAK.BSARK = ‘EXE’
AND USR02.USTYP = ‘B’
THEN ‘Excel (batch)’ WHEN VBAK.BSARK = ‘LDE’
THEN ‘EDI’ WHEN VBAK.BSARK = ‘FIOR’
THEN ‘CRM’ WHEN VBAK.BSARK = ‘CRP’
THEN ‘VMI’ WHEN VBAK.BSARK = ‘BAT’
THEN ‘EDI’
ELSE VBAK.BSARK
END AS AOE_MAPPING ,
USR02.USTYP AS USER_TYPE ,
STRUCT.“CC-CODE” AS CC_CODE ,
COMPAN.“CC-TEXT” AS CC_TEXT ,
STRUCT.“MU-CODE” AS MU_CODE ,
MANUNI.“MU-TEXT” AS MU_TEXT ,
STRUCT.“BR-CODE” AS BR_CODE ,
REGION.“BR-TEXT” AS BR_TEXT ,
KNA1.NAME1 AS “VBAK_NAME” ,
KNVH_1A.HKUNNR AS “KUNNR_1A” ,
KNA1_1A.NAME1 AS “NAME_1A” ,
KNVH_1B.HKUNNR AS “KUNNR_1B” ,
KNA1_1B.NAME1 AS “NAME_1B” ,
KNVH_1C.HKUNNR AS “KUNNR_1C” ,
KNA1_1C.NAME1 AS “NAME_1C” ,
KNVH_1D.HKUNNR AS “KUNNR_1D” ,
KNA1_1D.NAME1 AS “NAME_1D” ,
KNVH_1E.HKUNNR AS “KUNNR_1E” ,
KNA1_1E.NAME1 AS “NAME_1E” ,
KNVH_1F.HKUNNR AS “KUNNR_1F” ,
KNA1_1F.NAME1 AS “NAME_1F” ,
KNVH_1G.HKUNNR AS “KUNNR_1G” ,
KNA1_1G.NAME1 AS “NAME_1G” ,
KNVH_GCN.HKUNNR AS “GCN”,
KNA1_GCN.NAME1 AS “NAME_GCN” ,
KNVH_GCN.KUNNR AS “GCN_AHierarchy”,
/* KNVH_GCB.HKUNNR AS “GCB”,
KNA1_GCB.NAME1 AS “NAME_GCN” ,
KNVH_GCG.HKUNNR AS “GCG”,
KNA1_GCG.NAME1 AS “NAME_GCN” ,*/
OV_HEAD.OV_001 ,
OV_HEAD.OV_002 ,
OV_HEAD.OV_003 ,
OV_HEAD.OV_004 ,
OV_HEAD.OV_005 ,
OV_HEAD.OV_006 ,
OV_HEAD.OV_007 ,
OV_HEAD.OV_008 ,
OV_HEAD.OV_009 ,
OV_HEAD.OV_010 ,
OV_HEAD.OV_011 ,
OV_HEAD.OV_012 ,
OV_HEAD.OV_013 ,
OV_HEAD.OV_014 ,
OV_HEAD.OV_015 ,
OV_HEAD.OV_016 ,
OV_HEAD.OV_017 ,
OV_HEAD.OV_018 ,
OV_HEAD.OV_019 ,
OV_HEAD.OV_020 ,
OV_HEAD.OV_021 ,
OV_HEAD.OV_022 ,
OV_HEAD.OV_023 ,
OV_HEAD.OV_024 ,
OV_HEAD.OV_025 ,
OV_HEAD.OV_026 ,
OV_HEAD.OV_027 ,
OV_HEAD.OV_028 ,
OV_HEAD.OV_029 ,
OV_HEAD.OV_030 ,
OV_HEAD.OV_031 ,
OV_HEAD.OV_032 ,
OV_HEAD.OV_033
FROM “RAW_DATA”.VBAK AS VBAK
INNER JOIN “CELONIS”.“celonis.O2C.Views::_CEL_O2C_CASES” AS C ON VBAK.MANDT = C.MANDT
AND VBAK.VBELN = C.VBELN
LEFT JOIN “RAW_DATA”.TVAKT AS TVAKT ON 1=1
AND VBAK.MANDT = TVAKT.MANDT
AND VBAK.AUART = TVAKT.AUART
AND TVAKT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVLST ON 1=1
AND VBAK.MANDT = TVLST.MANDT
AND VBAK.LIFSK = TVLST.LIFSP
AND TVLST.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVFST ON 1=1
AND VBAK.MANDT = TVFST.MANDT
AND VBAK.FAKSK = TVFST.FAKSP
AND TVFST.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVKOT ON 1=1
AND VBAK.MANDT=TVKOT.MANDT
AND VBAK.VKORG = TVKOT.VKORG
AND TVKOT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVTWT ON 1=1
AND VBAK.MANDT = TVTWT.MANDT
AND VBAK.VTWEG = TVTWT.VTWEG
AND TVTWT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TSPAT ON 1=1
AND VBAK.MANDT = TSPAT.MANDT
AND VBAK.SPART = TSPAT.SPART
AND TSPAT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVGRT ON 1=1
AND VBAK.MANDT = TVGRT.MANDT
AND VBAK.VKGRP = TVGRT.VKGRP
AND TVGRT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVKBT ON 1=1
AND VBAK.MANDT = TVKBT.MANDT
AND VBAK.VKBUR = TVKBT.VKBUR
AND TVKBT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.T024B ON 1=1
AND VBAK.MANDT = T024B.MANDT
AND VBAK.KKBER = T024B.KKBER
AND VBAK.SBGRP = T024B.SBGRP
LEFT JOIN “RAW_DATA”.T014T ON 1=1
AND VBAK.MANDT = T014T.MANDT
AND VBAK.KKBER = T014T.KKBER
AND T014T.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.DD07T AS DD07T_VBTYP ON 1=1
AND DD07T_VBTYP.DOMVALUE_L = VBAK.VBTYP
AND DD07T_VBTYP.DOMNAME = ‘VBTYP’
AND DD07T_VBTYP.DDLANGUAGE = ‘E’
LEFT JOIN “RAW_DATA”.TVAK AS TVAK ON 1=1
AND VBAK.MANDT = TVAK.MANDT
AND VBAK.AUART = TVAK.AUART
LEFT JOIN “RAW_DATA”.DD07T AS DD07T_KLIMP ON 1=1
AND DD07T_KLIMP.DOMVALUE_L = TVAK.KLIMP
AND DD07T_KLIMP.DOMNAME = ‘KLIMP’
AND DD07T_KLIMP.DDLANGUAGE = ‘E’
LEFT JOIN “RAW_DATA”.TVAUT ON 1=1
AND VBAK.MANDT = TVAUT.MANDT
AND VBAK.AUGRU = TVAUT.AUGRU
AND TVAUT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.TVSBT ON 1=1
AND VBAK.MANDT = TVSBT.MANDT
AND VBAK.VSBED = TVSBT.VSBED
AND TVSBT.SPRAS = ‘E’
LEFT JOIN “RAW_DATA”.T001 ON 1=1
AND VBAK.MANDT = T001.MANDT
AND VBAK.BUKRS_VF = T001.BUKRS
LEFT JOIN “RAW_DATA”.USR02 AS USR02 ON VBAK.MANDT = USR02.MANDT
AND VBAK.ERNAM = USR02.BNAME
LEFT JOIN “RAW_DATA”."/BIC/PYCOMPANY" AS STRUCT ON VBAK.VKORG = STRUCT.“CC-CODE”
LEFT JOIN “RAW_DATA”."/BIC/TYCOMPANY" AS COMPAN ON STRUCT.“CC-CODE” = COMPAN.“CC-CODE”
LEFT JOIN “RAW_DATA”."/BIC/TYMANUNIT" AS MANUNI ON STRUCT.“MU-CODE” = MANUNI.“MU-CODE”
LEFT JOIN “RAW_DATA”."/BIC/TYBREGION" AS REGION ON STRUCT.“BR-CODE” = REGION.“BR-CODE”
LEFT JOIN RAW_DATA.KNA1 ON VBAK.KUNNR = KNA1.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1A ON KNVH_1A.HITYP = ‘A’
AND KNVH_1A.VKORG = VBAK.VKORG
AND KNVH_1A.VTWEG = VBAK.VTWEG
AND KNVH_1A.SPART = VBAK.SPART
AND KNVH_1A.DATAB <= CURRENT_DATE
AND KNVH_1A.DATBI >= CURRENT_DATE
AND KNVH_1A.KUNNR = VBAK.KUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1A ON KNVH_1A.HKUNNR = KNA1_1A.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1B ON KNVH_1B.HITYP = ‘A’
AND KNVH_1B.VKORG = KNVH_1A.VKORG
AND KNVH_1B.VTWEG = KNVH_1A.VTWEG
AND KNVH_1B.SPART = KNVH_1A.SPART
AND KNVH_1B.DATAB <= CURRENT_DATE
AND KNVH_1B.DATBI >= CURRENT_DATE
AND KNVH_1B.KUNNR = KNVH_1A.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1B ON KNVH_1B.HKUNNR = KNA1_1B.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1C ON KNVH_1C.HITYP = ‘A’
AND KNVH_1C.VKORG = KNVH_1B.VKORG
AND KNVH_1C.VTWEG = KNVH_1B.VTWEG
AND KNVH_1C.SPART = KNVH_1B.SPART
AND KNVH_1C.DATAB <= CURRENT_DATE
AND KNVH_1C.DATBI >= CURRENT_DATE
AND KNVH_1C.KUNNR = KNVH_1B.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1C ON KNVH_1C.HKUNNR = KNA1_1C.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1D ON KNVH_1D.HITYP = ‘A’
AND KNVH_1D.VKORG = KNVH_1C.VKORG
AND KNVH_1D.VTWEG = KNVH_1C.VTWEG
AND KNVH_1D.SPART = KNVH_1C.SPART
AND KNVH_1D.DATAB <= CURRENT_DATE
AND KNVH_1D.DATBI >= CURRENT_DATE
AND KNVH_1D.KUNNR = KNVH_1C.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1D ON KNVH_1D.HKUNNR = KNA1_1D.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1E ON KNVH_1E.HITYP = ‘A’
AND KNVH_1E.VKORG = KNVH_1D.VKORG
AND KNVH_1E.VTWEG = KNVH_1D.VTWEG
AND KNVH_1E.SPART = KNVH_1D.SPART
AND KNVH_1E.DATAB <= CURRENT_DATE
AND KNVH_1E.DATBI >= CURRENT_DATE
AND KNVH_1E.KUNNR = KNVH_1D.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1E ON KNVH_1E.HKUNNR = KNA1_1E.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1F ON KNVH_1F.HITYP = ‘A’
AND KNVH_1F.VKORG = KNVH_1E.VKORG
AND KNVH_1F.VTWEG = KNVH_1E.VTWEG
AND KNVH_1F.SPART = KNVH_1E.SPART
AND KNVH_1F.DATAB <= CURRENT_DATE
AND KNVH_1F.DATBI >= CURRENT_DATE
AND KNVH_1F.KUNNR = KNVH_1E.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1F ON KNVH_1F.HKUNNR = KNA1_1F.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_1G ON KNVH_1G.HITYP = ‘A’
AND KNVH_1G.VKORG = KNVH_1F.VKORG
AND KNVH_1G.VTWEG = KNVH_1F.VTWEG
AND KNVH_1G.SPART = KNVH_1F.SPART
AND KNVH_1G.DATAB <= CURRENT_DATE
AND KNVH_1G.DATBI >= CURRENT_DATE
AND KNVH_1G.KUNNR = KNVH_1F.HKUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_1G ON KNVH_1G.HKUNNR = KNA1_1G.KUNNR
LEFT JOIN “RAW_DATA”.“Y0000SD_OV_HEADE” AS OV_HEAD ON 1=1
AND OV_HEAD.MANDT = VBAK.MANDT
AND OV_HEAD.VBELN = VBAK.VBELN
AND OV_HEAD.VKORG = VBAK.VKORG
AND OV_HEAD.VTWEG = VBAK.VTWEG
AND OV_HEAD.SPART = VBAK.SPART
LEFT JOIN “CELONIS”.“celonis.O2C.Views::O2C_SHIPTO” as SHIPTO ON VBAK.MANDT = SHIPTO.MANDT
AND VBAK.VBELN = SHIPTO.VBELN
LEFT JOIN RAW_DATA.KNVH AS KNVH_GCN ON KNVH_GCN.HITYP = ‘F’
AND KNVH_1A.VKORG = KNVH_GCN.VKORG
AND KNVH_1A.VTWEG = KNVH_GCN.VTWEG
AND KNVH_1A.SPART = KNVH_GCN.SPART
AND KNVH_GCN.DATAB <= CURRENT_DATE
AND KNVH_GCN.DATBI >= CURRENT_DATE
AND (CASE
WHEN KNVH_1A.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1A.HKUNNR
WHEN KNVH_1B.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1B.HKUNNR
WHEN KNVH_1C.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1C.HKUNNR
WHEN KNVH_1G.HKUNNR = KNVH_GCN.KUNNR THEN KNVH_1G.HKUNNR
END) = KNVH_GCN.KUNNR
LEFT JOIN RAW_DATA.KNA1 AS KNA1_GCN ON KNVH_GCN.HKUNNR = KNA1_GCN.KUNNR
/*LEFT JOIN RAW_DATA.KNVH AS KNVH_GCB ON KNVH_GCB.HITYP = ‘F’
AND KNVH_1A.VKORG = KNVH_GCB.VKORG
AND KNVH_1A.VTWEG = KNVH_GCB.VTWEG
AND KNVH_1A.SPART = KNVH_GCB.SPART
AND KNVH_GCN.HKUNNR = KNVH_GCB.KUNNR
AND KNVH_GCB.DATAB <= CURRENT_DATE
AND KNVH_GCB.DATBI >= CURRENT_DATE
LEFT JOIN RAW_DATA.KNA1 AS KNA1_GCB ON KNVH_GCB.HKUNNR = KNA1_GCB.KUNNR
LEFT JOIN RAW_DATA.KNVH AS KNVH_GCG ON KNVH_GCG.HITYP = ‘F’
AND KNVH_1A.VKORG = KNVH_GCG.VKORG
AND KNVH_1A.VTWEG = KNVH_GCG.VTWEG
AND KNVH_1A.SPART = KNVH_GCG.SPART
AND KNVH_GCB.HKUNNR = KNVH_GCG.KUNNR
AND KNVH_GCG.DATAB <= CURRENT_DATE
AND KNVH_GCG.DATBI >= CURRENT_DATE
LEFT JOIN RAW_DATA.KNA1 AS KNA1_GCG ON KNVH_GCG.HKUNNR = KNA1_GCG.KUNNR */
)
WHERE VBELN = ‘0109134223’

The select statement around the “real select statement with the join” is just to have the code only calculate the result for one example where i know there is a match with 1A and 1C.
Do you have another idea on how to solve this? If you need any input feel free to ask me.

Best regards,
Marcel

Hi Marcel,

I had a look at the code you sent. In the inner select statement you rename “KNVH_GCN.KUNNR” as “GCN_AHierarchy”. So, in the outer select statement you could try using the code from my first message in the where statement, just using GCN_AHierarchy instead of KNVH_GCN.KUNNR.

The WHERE statement at the end would now be:

WHERE VBELN = ‘0109134223’ AND
(CASE WHEN KNVH_1A.HKUNNR = GCN_AHierarchy THEN KNVH_1A.HKUNNR
WHEN KNVH_1B.HKUNNR = GCN_AHierarchy THEN KNVH_1B.HKUNNR
WHEN KNVH_1C.HKUNNR = GCN_AHierarchy THEN KNVH_1C.HKUNNR
….
WHEN KNVH_1G.HKUNNR = GCN_AHierarchy THEN KNVH_1G.HKUNNR) = GCN_AHierarchy

Please not it may be nessary to write GCN_AHierarchy in quotes, as that is what is returned by the inner select statement.

Does this solve the problem?

Best wishes,
Calandra

Hello Calandra,

thank you so much for your answers and for taking all the effort for helping me.

THe select i biult around the inner select Statement was only for “Display purposes”
The inner select is actually my view on VBAK Table that will be used in the Celonis datamodel.

As a result I Need a Statement that works without the statement around the inner one. Do you understand what i mean?

Thank you again!!

Hi Marcel,

Does the where statement I recommended in my last reply work on your “display purposes” statement. I.e. does it successfully exclude second matches?

If it doesn’t work, then that’s probably because I forgot to use the aliases for the column names defined in the inner select statement in the outer where statement. Sorry about that. In my code suggestion bellow I changed it.

As you are joining on raw data tables in the inner select statement (which I assume is the code that defines the view), I don’t think you can avoid using a second select statement to throw out redundant rows as this is a second step which is applied on the whole table. (Trying to make the “LEFT JOIN RAW_DATA.KNA1 AS…” part conditional of which part matches GCN_AHierachy would get very messy and not necessarily improve performance, as I think you would have to join the RAW_DATA.KNVH AS KNVH_GCN on to it first to check for matches. This would be very inefficient and hard to implement)

If I understand you correctly, you want change the view on VBAK, so that it only includes the first match between KUNNR_A, …, KUNNR_G and GCN_AHierachy?

In that case why don’t you use two select statements in the definition of the view itself? In SAP views have a slightly different syntax, for instance, all " need to be escaped with a backslash so if the view won’t activate you might have to play around with the code a bit to make sure all " are in the write place and they are all escaped (apart from the final one of course that closes the query).

The outer select statement would be:

query = "SELECT * FROM (SELECT << insert old code to make the view here>>) AS VBAK_VIEW

WHERE VBAK_VIEW.VBELN = ‘0109134223’ AND

(CASE WHEN VBAK_VIEW. KUNNR_1A = VBAK_VIEW.GCN_AHierarchy THEN VBAK_VIEW. KUNNR_1A WHEN VBAK_VIEW. KUNNR_1B = VBAK_VIEW.GCN_AHierarchy THEN VBAK_VIEW. KUNNR_1B

WHEN VBAK_VIEW. KUNNR_1C = GCN_AHierarchy THEN VBAK_VIEW. KUNNR_1C

….

WHEN VBAK_VIEW. KUNNR_1G = GCN_AHierarchy THEN VBAK_VIEW. KUNNR_1G) = VBAK_VIEW. GCN_AHierarchy ";

Best wishes,

Calandra