Oracle SQL recursion -- Long-time problem I wish to resolve

Question

I use Oracle SQL Developer and have been having a tough time working with recursion. I have orders aa-1, bb-1, and cc-1. cc-1 is issued to bb-1 and bb-1 is issued to aa-1 per my pictures:

recursion_have_to_want

I want to be able to create a dataset where I can filter in the BI for an order (in this case aa-1). Naturally I have a lot of orders so having an extra parent column would be ideal as the user will be passing a parameter with the order number to filter the dataset.

recursion_have_to_want_parent

I hope this is an adequate breakdown of what I'm trying to accomplish. I'll be watching this post as this problem has been present for some time.

Thanks for any assistance.

---EDIT---

Alright, I apologize and understand my errors in posting. Here is the code that works for a singular case but I am stuck trying to get it to work for the entire dataset (not just aa-1). This single query takes 72 seconds. We use AWS Quicksight, and I ultimately would like to be able to create a relatively quick-loading dataset for work orders up to -at least- the last 3 months.

WITH order_hierarchy AS (
        SELECT  we.wip_entity_name AS work_order,
                msi.segment1 AS part_number,
                mut.serial_number,
                mmt.transaction_date
        FROM    apps.mtl_system_items_b msi
                INNER JOIN apps.mtl_material_transactions mmt
                    ON msi.organization_id = mmt.organization_id
                    AND msi.inventory_item_id = mmt.inventory_item_id
                INNER JOIN apps.wip_entities we
                    ON mmt.organization_id = we.organization_id
                    AND mmt.transaction_source_id = we.wip_entity_id
                LEFT OUTER JOIN apps.mtl_unit_transactions_all_v mut
                    ON mmt.transaction_id = mut.transaction_id
                    AND mmt.organization_id = mut.organization_id
                AND mmt.inventory_item_id = mut.inventory_item_id
        WHERE   TRUNC(mmt.transaction_date) >= ADD_MONTHS(TRUNC(TO_DATE(SYSDATE),'Q'),-12)
                AND msi.serial_number_control_code = 5
                AND mmt.transaction_type_id = 35
                AND CASE
                        WHEN mut.serial_number IS NOT NULL THEN mmt.transaction_quantity / ABS(mmt.transaction_quantity)
                        ELSE mmt.transaction_quantity
                END = -1      
)
SELECT  *
FROM    order_hierarchy
START WITH work_order = 'aa-1'
CONNECT BY PRIOR serial_number = work_order; 

I will keep adding to this, but I at least want to get the query in here.

--EDIT2-- Here is the sample data (but again, my trouble lies in doing this in a reasonable load time with hundreds of work orders)

CREATE TABLE table_name (work_order, part_number, serial_number, transaction_date) AS
  SELECT 'aa-1', 'A1', '112233', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'aa-1', 'A2', '112234', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'aa-1', 'B1', 'bb-1', '18-JAN-24'   FROM DUAL UNION ALL
  SELECT 'bb-1', 'S1', '998877', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'bb-1', 'S2', '998878', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'bb-1', 'C1', 'cc-1', '18-JAN-24'   FROM DUAL UNION ALL
  SELECT 'cc-1', 'X1', '998877', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'cc-1', 'X2', '998878', '18-JAN-24' FROM DUAL;

CTE for sample data:

WITH order_hierarchy AS (
        SELECT  work_order,
                part_number,
                serial_number,
                transaction_date
        FROM    orders o      
)
SELECT  *
FROM    order_hierarchy
START WITH work_order = 'aa-1'
CONNECT BY PRIOR serial_number = work_order;

Answer 1

You appear to want either a hierarchical query and CONNECT_BY_ROOT:

SELECT "order",
       part,
       sn,
       CONNECT_BY_ROOT "order" AS parent
FROM   table_name
START WITH "order" = 'aa-1'
CONNECT BY PRIOR sn = "order";

Or a recursive query:

WITH orders ("order", part, sn, parent) AS (
  SELECT "order", part, sn, "order"
  FROM   table_name
  WHERE  "order" = 'aa-1'
UNION ALL
  SELECT t."order", t.part, t.sn, o.parent
  FROM   orders o
         INNER JOIN table_name t
         ON o.sn = t."order"
)
SELECT *
FROM   orders;

Which, for the sample data:

CREATE TABLE table_name ("order", part, sn) AS
  SELECT 'aa-1', 'A1', '112233' FROM DUAL UNION ALL
  SELECT 'aa-1', 'A2', '112234' FROM DUAL UNION ALL
  SELECT 'aa-1', 'B1', 'bb-1'   FROM DUAL UNION ALL
  SELECT 'bb-1', 'S1', '998877' FROM DUAL UNION ALL
  SELECT 'bb-1', 'S2', '998878' FROM DUAL UNION ALL
  SELECT 'bb-1', 'C1', 'cc-1'   FROM DUAL UNION ALL
  SELECT 'cc-1', 'X1', '998877' FROM DUAL UNION ALL
  SELECT 'cc-1', 'X2', '998878' FROM DUAL;

Note: order is a reserved word and you should not use it as an identifier; if you do then you must use quoted identifers everywhere it is used.

Both output:

order	PART	SN	PARENT
aa-1	A1	112233	aa-1
aa-1	A2	112234	aa-1
aa-1	B1	bb-1	aa-1
bb-1	S1	998877	aa-1
bb-1	S2	998878	aa-1
bb-1	C1	cc-1	aa-1
cc-1	X1	998877	aa-1
cc-1	X2	998878	aa-1

fiddle