Connect by prior / start with

[flight]

Versucht zu migrieren start with & connect by prior, aber nicht sicher.Benötigen Sie Hilfe
– Oracle:

  SELECT CUST_NO
      FROM CUSTOMER_AR
     START WITH CUST_NO = CUST_NUM
    CONNECT BY PRIOR CUST_PARENT_NO = CUST_NO
    UNION
    SELECT CUST_NO
      FROM CUSTOMER_AR
     START WITH CUST_NO = CUST_NUM
    CONNECT BY PRIOR CUST_PAYOR_NO = CUST_NO;

– Postgresql:

WITH RECURSIVE tmp1 AS
(SELECT CUST_NO
  FROM CUSTOMER_AR
  UNION
  SELECT CUST_NO
  FROM CUSTOMER_AR)
  WHERE CUST_NO IN 
  (SELECT C.CUST_NO FROM GCARS.CUSTOMER C WHERE CUST_NO = CUST_NUM)
  JOIN tmp1 ON C.CUST_PARENT_NO = tmp1.CUST_PAYOR_NO
  select CUST_NO from tmp1

[castorp]

Die Oracle Abfrage sieht etwas merkwürdig aus, aber ich glaube eine rekursive Abfrage die beide “parent” Spalten verwendet müsste eigentlich äquivalent sein:

WITH RECURSIVE tmp1 AS (
  SELECT cust_no
  FROM customer_ar 
  WHERE cust_no = cust_num
  
  UNION ALL 
  
  SELECT c.cust_no
  FROM customer_ar c
    JOIN tmp1 p ON p.cust_no in (c.cust_parent_no, c.cust_payor_no)
)
select cust_no
from tmp1;

Wenn das ein anderes Ergebnis liefert als Oracle’s UNION, dann kann man die Struktur der Oracle Abfrage auch “kopieren”:

WITH RECURSIVE tmp1 AS (

  SELECT cust_no
  FROM customer_ar 
  WHERE cust_no = cust_num
  UNION ALL 
  SELECT c.cust_no
  FROM customer_ar c
    JOIN tmp1 p ON p.cust_no = c.cust_parent_no

), tmp2 as (

  SELECT cust_no
  FROM customer_ar 
  WHERE cust_no = cust_num
  UNION ALL 
  SELECT c.cust_no
  FROM customer_ar c
    JOIN tmp2 p ON p.cust_no = c.cust_payor_no
)
select cust_no
from tmp1
union 
select cust_no
from tmp2;