Recursive query design - Oracle SQL

Question

I am looking to create a recursive query that is able to completely map a series of replenishments, to a top level requirement.

Below is some detailed information regarding my problem statement:

I am attempting to design a recursive solution to calculate all subsequent replenishments (and their levels) give a top level requirement, where a requirement is defined as a production or planned order (a MIN type)

I have created this SQLFIDDLE using the below query:

-- schema
CREATE TABLE tblInputs (
    Type VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256),
    Direction VARCHAR(256)
);

CREATE TABLE Requirements (
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256)
);

CREATE TABLE ReqRep (
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256),
    Rep_ID VARCHAR(256)
);

CREATE TABLE Replenishments (
    Rep_ID VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256)
);


-- data
INSERT INTO tblInputs (Type, Order_No, Planned_No, Purchase_No, Direction)

SELECT 'Purchase', NULL, NULL, 'PO9000124798', 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908851', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908852', NULL, 'PLUS' FROM DUAL 
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908854', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908855', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Build', 'O103916639', NULL, NULL, 'MIN' FROM DUAL
UNION ALL
SELECT 'Production', 'O103962037', NULL, NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Production', 'O103933200', NULL, NULL, 'PLUS' FROM DUAL
;

INSERT INTO Requirements (Order_No, Planned_No, Req_ID, Req_No)
SELECT NULL, 'PL191908851', 'ABA', '36' FROM DUAL UNION ALL
SELECT NULL, 'PL191908852', 'CC',  '93' FROM DUAL UNION ALL
SELECT NULL, 'PL191908853', 'BBA', '27' FROM DUAL UNION ALL
SELECT NULL, 'PL191908854', 'EWE', '42' FROM DUAL UNION ALL
SELECT NULL, 'PL191908855', 'WWW', '13' FROM DUAL UNION ALL
SELECT NULL, 'PL191908856', 'EEE', '33' FROM DUAL UNION ALL
SELECT NULL, 'PL191909922', 'GFW', '99' FROM DUAL UNION ALL
SELECT NULL, 'PL191910022', 'GFT', '23' FROM DUAL UNION ALL
SELECT NULL, 'PL192010120', 'THE', '54' FROM DUAL UNION ALL
SELECT 'O103962037',  NULL, 'BDD', '37' FROM DUAL UNION ALL
SELECT 'O103933200',  NULL, 'DFA', '63' FROM DUAL UNION ALL
SELECT 'O103547812',  NULL, 'ADS', '45' FROM DUAL UNION ALL
SELECT 'O103547415',  NULL, 'DWQ', '94' FROM DUAL UNION ALL
SELECT 'O103654787',  NULL, 'QZX', '96' FROM DUAL UNION ALL
SELECT 'O103214217',  NULL, 'NFD', '20' FROM DUAL UNION ALL
SELECT 'O103215320',  NULL, 'GBV', '33' FROM DUAL UNION ALL
SELECT 'O106212219',  NULL, 'ERQ', '22' FROM DUAL UNION ALL
SELECT 'O103215320',  NULL, 'MRP', '11' FROM DUAL
;

INSERT INTO ReqRep (Req_ID, Req_No, Rep_ID)
SELECT 'ABA', '36', '7736' FROM DUAL UNION ALL
SELECT 'CCC', '93', '6686' FROM DUAL UNION ALL
SELECT 'BBA', '27', '5710' FROM DUAL UNION ALL
SELECT 'EWE', '42', '7634' FROM DUAL UNION ALL
SELECT 'WWW', '13', '9393' FROM DUAL UNION ALL
SELECT 'EEE', '33', '8442' FROM DUAL UNION ALL
SELECT 'GFW', '99', '5758' FROM DUAL UNION ALL
SELECT 'GFT', '23', '5988' FROM DUAL UNION ALL
SELECT 'THE', '54', '6748' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7123' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7124' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7125' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7126' FROM DUAL UNION ALL
SELECT 'DFA', '63', '7125' FROM DUAL UNION ALL
SELECT 'ADS', '45', '5855' FROM DUAL UNION ALL
SELECT 'DWQ', '80', '9419' FROM DUAL UNION ALL
SELECT 'QZX', '96', '5748' FROM DUAL UNION ALL
SELECT 'NFD', '20', '7055' FROM DUAL UNION ALL
SELECT 'ERQ', '22', '7736' FROM DUAL UNION ALL
SELECT 'MRP', '11', '7736' FROM DUAL UNION ALL
SELECT 'GBV', '33', '9999' FROM DUAL
;

INSERT INTO Replenishments(Rep_ID, Order_No, Planned_No, Purchase_No)
SELECT '7736', NULL, NULL, 'PO9000124799' FROM DUAL UNION ALL
SELECT '6686', NULL, NULL, 'PO9000124800' FROM DUAL UNION ALL
SELECT '5710', NULL, NULL, 'PO9000124801' FROM DUAL UNION ALL
SELECT '7634', NULL, NULL, 'PO9000124802' FROM DUAL UNION ALL
SELECT '9393', NULL, NULL, 'PO9000124803' FROM DUAL UNION ALL
SELECT '8442', NULL, NULL, 'PO9000124804' FROM DUAL UNION ALL
SELECT '5758', NULL, NULL, 'PO9000124805' FROM DUAL UNION ALL
SELECT '5988', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
SELECT '6748', NULL, NULL, 'PO9000124807' FROM DUAL UNION ALL
SELECT '7123', 'O103654787', NULL, NULL FROM DUAL UNION ALL
SELECT '7124', 'O103214217', NULL, NULL FROM DUAL UNION ALL
SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
SELECT '7126', 'O106212219', NULL, NULL FROM DUAL UNION ALL
SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
SELECT '5855', NULL, 'PL192010120', NULL FROM DUAL UNION ALL
SELECT '9419', NULL, 'PL121122221', NULL FROM DUAL UNION ALL
SELECT '5748', NULL, 'PL272634123', NULL FROM DUAL UNION ALL
SELECT '7055', NULL, 'PL983002032', NULL FROM DUAL UNION ALL
SELECT '9999', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
SELECT '1111', NULL, NULL, 'PO9000124806' FROM DUAL
;

Using the below query against my SQLFIDDLE, I can essentially get the first "level":

WITH

  -- Use one order as an example
  -- This will eventually be used for many orders at once
  currOrder AS
    (
      SELECT
        tblInputs.Order_No
      FROM
        tblInputs
      WHERE
        Direction = 'PLUS'
        AND
        (Type = 'Production'  OR Type = 'Planned')
        -- Added this for example purposes only!
        AND
        Order_No LIKE '%O103962037%'
     ),

    -- Now get the details about this order being a REQUIREMENT
   Req AS
     (
       SELECT
        -- unique identifier(s), unfortunately two fields combine to make the primary key
         Req_ID,
         Req_No

       FROM
         Requirements
           INNER JOIN currOrder ON currOrder.Order_No = Requirements.Order_No
      ),

   -- This is a giant bridge table of requirements, and replenishments
   -- A requirement can be made up of many replenishments, and a replenishment can satisfy many requirements
   -- Find the replenishments for our specific requirement
   Req_Rep AS
     (
       SELECT DISTINCT
         ReqRep.REP_ID

       FROM
         ReqRep
           INNER JOIN Req ON ReqRep.Req_ID = Req.Req_ID AND ReqRep.Req_No = Req.Req_No
      ),

  -- Now, grab the replenishment data that we care about, and its details
  -- Only one field will be filled in at a time depending on what the replenishment type is
   Rep AS
     (
       SELECT DISTINCT
         Req_Rep.Rep_ID,
         CASE
           WHEN Replenishments.Order_No IS NULL AND Replenishments.Planned_No IS NULL THEN Replenishments.Purchase_No
           WHEN Replenishments.Order_No IS NULL AND Replenishments.Planned_No IS NOT NULL AND Replenishments.Purchase_No IS NULL THEN Replenishments.Planned_No
           WHEN Replenishments.Order_No IS NOT NULL AND Replenishments.Planned_No IS NULL AND Replenishments.Purchase_No IS NULL THEN Replenishments.Order_No
         ELSE NULL END AS The_Number

        FROM
         Req_Rep
           INNER JOIN Replenishments on Replenishments.Rep_ID = Req_Rep.Rep_ID
      )

   -- Grab the results
   SELECT DISTINCT * FROM Rep 

Using that query, I get this dataset:

REP_ID  THE_NUMBER
7123    O103654787
7125    O103215320
7124    O103214217
7126    O106212219

Which shows me that for a specific requirement (specified by its Order_No), these are the replenishments that satisfy it. Then, for those replenishments, this is the metadata that is associated with them.

I now need to take The_Number and essentially repeat the entire process. And for each replenishment that is an production or planned order, continue to find all of the replenishments (since, if a replenishment is a production or planned order, it may have additional replenishments)

The goal dataset would look like this:

|    Number   | Req_ID | Req_No | Rep_ID |    Details   | Level |
|:-----------:|:------:|:------:|:------:|:------------:|:-----:|
|  O103962037 |   BDD  |   377  |  7123  |  O103654787  |   1   |
|  O103962037 |   BDD  |   377  |  7124  |  O103214217  |   1   |
|  O103962037 |   BDD  |   377  |  7125  |  O103215320  |   1   |
|  O103962037 |   BDD  |   377  |  7126  |  O106212219  |   1   |
|  O103654787 |   QZX  |   96   |  5748  |  PL272634123 |   2   |
|  O103214217 |   NFD  |   20   |  7055  |  PL983002032 |   2   |
|  O103215320 |   GBV  |   33   |  9999  | PO9000124806 |   2   |
|  O106212219 |   ERQ  |   22   |  7736  | PO9000124799 |   2   |
| PL272634123 |   MRP  |   99   |  1111  | PO9000124806 |   3   |
| PL983002032 |   EWE  |   22   |  1111  | PO9000124806 |   3   |

I believe I have already achieved the recursive anchor in my query, but I cannot determine how to build the rest. Any advice is appreciated.

I have reviewed these posts:

• Post 1
• Post 2
• Post 3

I think the line I need is Rep.The_Number = Requirements.Order_No, but I don't know how to define that.

Answer 1

I figured a solution that I hope will help at least as a starting point for your more complex version, assuming I understood the data model correctly. SQLFiddle is here. I had to modify the sample data as I could not replicate your expected results based on the provided sample.

The SQL I came up with is this:

WITH t as (
SELECT coalesce(r.planned_no, r.Order_No) order_no,
       r.Req_ID,
       r.Req_No,
       rr.REP_ID,
       coalesce(rp.purchase_no, rp.planned_no, rp.order_no) details,
       0 replevel
  FROM requirements r
  JOIN ReqRep rr ON rr.Req_ID = r.Req_ID AND rr.Req_No = r.Req_No
  JOIN Replenishments rp on rp.Rep_ID = rr.Rep_ID),
det_t  (order_no, req_id, req_no, rep_id, details, replevel, rep_path) as (
SELECT t.Order_No,
       t.Req_ID,
       t.Req_No,
       t.REP_ID,
       t.details,
       1 replevel,
       t.order_no rep_path
  FROM t
 WHERE Order_No LIKE '%O103962037%'
 UNION ALL
 SELECT t.Order_No,
       t.Req_ID,
       t.Req_No,
       t.REP_ID,
       t.details,
       d.replevel + 1,
       rep_path || '=>' || t.order_no rep_path
  FROM det_t d
  JOIN t ON t.order_no = d.details
)
select order_no,
       req_id,
       req_no,
       rep_id,
       details,
       replevel,
       rep_path
  from det_t
 order by replevel, order_no;

Sample execution as follows:

FSITJA@db01 2019-07-16 09:59:00> WITH t as (
  2  SELECT coalesce(r.planned_no, r.Order_No) order_no,
  3         r.Req_ID,
  4         r.Req_No,
  5         rr.REP_ID,
  6         coalesce(rp.purchase_no, rp.planned_no, rp.order_no) details,
  7         0 replevel
  8    FROM requirements r
  9    JOIN ReqRep rr ON rr.Req_ID = r.Req_ID AND rr.Req_No = r.Req_No
 10    JOIN Replenishments rp on rp.Rep_ID = rr.Rep_ID),
 11  det_t  (order_no, req_id, req_no, rep_id, details, replevel, rep_path) as (
 12  SELECT t.Order_No,
 13         t.Req_ID,
 14         t.Req_No,
 15         t.REP_ID,
 16         t.details,
 17         1 replevel,
 18         t.order_no rep_path
 19    FROM t
 20   WHERE Order_No LIKE '%O103962037%'
 21   UNION ALL
 22   SELECT t.Order_No,
 23         t.Req_ID,
 24         t.Req_No,
 25         t.REP_ID,
 26         t.details,
 27         d.replevel + 1,
 28         rep_path || '=>' || t.order_no rep_path
 29    FROM det_t d
 30    JOIN t ON t.order_no = d.details
 31  )
 32  select order_no,
 33         req_id,
 34         req_no,
 35         rep_id,
 36         details,
 37         replevel,
 38         rep_path
 39    from det_t
 40   order by replevel, order_no;

ORDER_NO    REQ_ID  REQ_NO  REP_ID  DETAILS           REPLEVEL REP_PATH
----------- ------- ------- ------- --------------- ---------- -------------------------------------
O103962037  BDD     37      7123    O103654787               1 O103962037
O103962037  BDD     37      7124    O103214217               1 O103962037
O103962037  BDD     37      7125    O103215320               1 O103962037
O103962037  BDD     37      7126    O106212219               1 O103962037
O103214217  NFD     20      7055    PL191908854              2 O103962037=>O103214217
O103215320  GBV     33      9999    PO9000124806             2 O103962037=>O103215320
O103215320  MRP     11      7736    PO9000124799             2 O103962037=>O103215320
O103654787  QZX     96      5748    PL272634123              2 O103962037=>O103654787
O106212219  ERQ     22      7736    PO9000124799             2 O103962037=>O106212219
PL191908854 EWE     42      7634    PO9000124802             3 O103962037=>O103214217=>PL191908854
PL272634123 XYZ     36      8888    PL2222222222             3 O103962037=>O103654787=>PL272634123

11 rows selected.

I think there is a mismatch between sample data and expected result. On your expected result, unless I am misinterpreting the data model, the level 3 rows (MRP and EWE) REQ_NO do not match what is in the sample data.

MRP has Req_No = 99 in the exptected result and in the insert it has Req_No = 11, connecting it to level 2 under Replenishment level 1 Rep_Id = 7125.

EWE has Req_No = 22 in the expected result and in the insert it has Req_No = 42, and it connects from level 1 under Replenishment Rep_Id = 7055. However in table Requirements EWE has Planned_No = 'PL191908854' while in level 2 Replenishment Rep_Id = 7055 is pointing to Order_No = 'PL983002032'.

If I change the provided sample data on Replinishment Rep_Id = 7055 from Order_No = 'PL983002032' to 'PL191908854' it shows correctly EWE under level 3.

This is what I changed in the Replenishment table insert (it is reflected in the SQLFiddle): Added (just to illustrate a level 3 connection that works):

SELECT '8888', NULL, NULL, 'PL2222222222' FROM DUAL

Modified (to get EWE to show in level 3 correctly):

SELECT '7055', NULL, 'PL191908854', NULL FROM DUAL UNION ALL

Answer 2

Here is what I was able to come up with. This comes very close to your expected results for that given order except that you have a duplicate for O103215320 in the Requirements table that may be an error in your data.

I am a little confused about how the tblInputs table fits into this. My query below is not using that table at all.

with orders as (
select nvl(Requirements.order_no,Requirements.Planned_No) order_no,
       Requirements.Req_ID,
       Requirements.Req_No,
       Replenishments.Rep_ID,
       coalesce(Replenishments.Order_No,Replenishments.Planned_No,Replenishments.Purchase_No) Details
from Requirements
join ReqRep
  on ReqRep.Req_ID = Requirements.Req_ID
 and ReqRep.Req_No = Requirements.Req_No
join Replenishments
  on Replenishments.Rep_ID = ReqRep.Rep_ID)

select order_no,
       Req_ID,
       Req_No,
       Rep_ID,
       Details,
       lvl
from (
select orders.*,
       connect_by_root orders.Order_No root_order,
       level lvl
from orders
connect by prior orders.details = orders.order_no)
where root_order like '%O103962037%'
order by lvl,order_no

The above query is giving the following results:

+-------------+--------+--------+--------+--------------+-----+
|  ORDER_NO   | REQ_ID | REQ_NO | REP_ID |   DETAILS    | LVL |
+-------------+--------+--------+--------+--------------+-----+
| O103962037  | BDD    |     37 |   7123 | O103654787   |   1 |
| O103962037  | BDD    |     37 |   7125 | O103215320   |   1 |
| O103962037  | BDD    |     37 |   7124 | O103214217   |   1 |
| O103962037  | BDD    |     37 |   7126 | O106212219   |   1 |
| O103214217  | NFD    |     20 |   7055 | PL983002032  |   2 |
| O103215320  | MRP    |     11 |   7736 | PO9000124799 |   2 |
| O103215320  | GBV    |     33 |   9999 | PO9000124806 |   2 |
| O103654787  | QZX    |     96 |   5748 | PL272634123  |   2 |
| O106212219  | ERQ    |     22 |   7736 | PO9000124799 |   2 |
| PL272634123 | MRP    |     99 |   1111 | PO9000124806 |   3 |
| PL983002032 | EWE    |     22 |   1111 | PO9000124806 |   3 |
+-------------+--------+--------+--------+--------------+-----+