Hi Marcel,
Im 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 Im 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 doesnt 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

---

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 doesnt work, then thats 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 dont 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 dont 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 wont 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

---