Aggregating or Bundle a Many to Many Relationship in SQL Developer

Question

So I have 1 single table with 2 columns : Sales_Order called ccso, Arrangement called arrmap The table has distinct values for this combination and both these fields have a Many to Many relationship

• 1 ccso can have Multiple arrmap
• 1 arrmap can have Multiple ccso

All such combinations should be considered as one single bundle

Objective : Assign a final map to each of the Sales Order as the Largest Arrangement in that Bundle

Example: ccso : 100-10015 has 3 arrangements --> Now each of those arrangements have a set of Sales Orders --> Now those sales orders will also have a list of other arrangements and so on (Image : 1)

Therefore the answer definitely points to something recursively checking. Ive managed to write the below code / codes and they work as long as I hard code a ccso in the where clause - But I don't know how to proceed after this now. (I'm an accountant by profession but finding more passion in coding recently) I've searched the forums and web for things like

• Recursive CTEs,
• many to many aggregation
• cartesian product etc and I'm sure there must be a term for this which I don't know yet. I've also tried I have to use sqldeveloper or googlesheet query and filter formulas sqldeveloper has restrictions on on some CTEs. If recursive is the way I'd like to know how and if I can control the depth to say 4 or 5 iterations

Ideally I'd want to update a third column with the final map if possible but if not, then a select query result is just fine

Codes I've tried

Code 1: As per Screenshot

    WITH    a1(ccso, amap) AS 
            (SELECT  distinct a.ccso, a.arrmap
            FROM    rg_consol_map2 A
            WHERE   a.ccso = '100-10115' -- this condition defines the ultimate ancestors in your chain, change it as appropriate
            UNION ALL
            SELECT  m.ccso, m.arrmap
            FROM    rg_consol_map2 m
            JOIN    a1
            ON      M.arrmap = a1.amap --  or m.ccso=a1.ccso
            ) /*if*/ CYCLE amap SET nemap TO 1 /*else*/ DEFAULT 0 
SELECT DISTINCT amap FROM (SELECT ccso, amap FROM a1 ORDER BY 1 DESC) WHERE ROWNUM = 1 

In this the main challenge is how to remove the hardcoded ccso and do a join for each of the ccso

Code 2 : Manual CTEs for depth Here again the join outside the CTE gives me an error and sqldeveloper does not allow WITH clause with UPDATE statement - only works for select and cannot be enclosed within brackets as subtable

SELECT distinct ccso FROM
(
WITH ar1 AS
(SELECT distinct arrmap
FROM rg_consol_map
WHERE ccso = a.ccso
)
,so1 AS
(SELECT DISTINCT ccso
FROM rg_consol_map
WHERE arrmap IN (SELECT arrmap FROM ar1)
)
,ar2 AS
(SELECT DISTINCT ccso FROM rg_consol_map
where arrmap IN (select distinct arrmap FROM rg_consol_map
WHERE ccso IN (SELECT ccso FROM so1)
))
SELECT ar1.arrmap, NULL ccso FROM ar1
union all
SELECT null, ar2.ccso FROM ar2
UNION ALL
SELECT NULL arrmap, so1.ccso FROM so1
)

Am I Missing something here or is there an easier way to do this? I read something about MERGE and PROC SQL JOIN but was unable to get them to work but if that's the way to go ahead I will try further if someone can point me in the direction (Image : 2)

(CSV File : [3])

Edit : Fixing CSV file link https://github.com/karan360note/karanstackoverflow.git I suppose can be downloaded from here IC mapping many to many.csv

Oracle 11g version is being used

Answer 1

Apologies in advance for the wall of text.

Your problem is a complex, multi-layered Many-to-Many query; there is no "easy" solution to this, because that is not a terribly ideal design choice. The safest best does literally include multiple layers of CTE or subqueries in order to achieve all the depths you want, as the only ways I know to do so recursively rely on an anchor column (like "parentID") to direct the recursion in a linear fashion. We don't have that option here; we'd go in circles without a way to track our path.

Therefore, I went basic, and with several subqueries. Every level checks for a) All orders containing a particular ARRMAP item, and then b) All additional items on those orders. It's clear enough for you to see the logic and modify to your needs. It will generate a new table that contains the original CCSO, the linking ARRMAP, and the related CCSO. Link: https://pastebin.com/un70JnpA

This should enable you to go back and perform the desired updates you want, based on order # or order date, etc... in a much more straightforward fashion. Once you have an anchor column, a CTE in the future is much more trivial (just search for "CTE recursion tree hierarchy").

SELECT DISTINCT 
    CCSO, RELATEDORDER
FROM myTempTable
WHERE CCSO = '100-10115';     /* to find all orders by CCSO, query SELECT DISTINCT RELATEDORDER */
--WHERE ARRMAP = 'ARR10524';  /* to find all orders by ARRMAP, query SELECT DISTINCT CCSO */

EDIT:

To better explain what this table generates, let me simplify the problem.

If you have order

• A with arrangements 1 and 2;
• B with arrangement 2, 3; and
• C with arrangement 3;

then, by your initial inquiry and image, order A should related to orders B and C, right? The query generates the following table when you SELECT DISTINCT ccso, relatedOrder:

+-------+--------------+
|  CCSO | RelatedOrder |
+----------------------+
|   A   |     B        |
|   A   |     C        |
+----------------------+
|   B   |     C        |
|   B   |     A        |
+----------------------+
|   C   |     A        |
|   C   |     B        |
+-------+--------------+

You can see here if you query WHERE CCSO = 'A' OR RelatedOrder = 'A', you'll get the same relationships, just flipped between the two columns.

+-------+--------------+
|  CCSO | RelatedOrder |
+----------------------+
|   A   |     B        |
|   A   |     C        |
+----------------------+
|   B   |     A        |
+----------------------+
|   C   |     A        |
+-------+--------------+

So query only CCSO or RelatedOrder.

As for the results of WHERE CCSO = '100-10115', see image here, which includes all the links you showed in your Image #1, as well as additional depths of relations.