Oracle Connect By hierarchy query returns top level parent but child part is null

Question

I have a CONNECT BY hierarchal query that starts with a known child part and returns the top level parent(s). It runs quickly and works great except for one thing: it returns null values for the child part.

WITH

c AS(
SELECT
    d.C_ID,
    d.PART_ID
    FROM T_MASTER_DAT d
    WHERE
    d.C_ID = 1124609156
)

SELECT DISTINCT
c.C_ID CHILD_ID,
c.PART_ID CHILD_PART,
s.C_ID_1 TOP_PARENT_ID,
d2.PART_ID TOP_PARENT_PART,
d2.CUR_FLAG TOP_PARENT_CUR_FLAG,
d2.CANCEL TOP_PARENT_CANCEL,
d2.UNIT,
d2.OPP_BR_UNIT
FROM T_MASTER_STR s
LEFT JOIN c on c.C_ID = s.C_ID_2
JOIN T_MASTER_DAT d2 on d2.C_ID = s.C_ID_1
WHERE
d2.OPP_BR_UNIT is not null and
d2.CUR_FLAG = 'y' and
d2.CANCEL = 'n'
CONNECT BY PRIOR s.C_ID_1 = s.C_ID_2
START WITH s.C_ID_2 = c.C_ID

I simplified things a bit here. In the real environment table c is the result of a few table joins so it's more complex but the gist of table c is to get that C_ID number and use it to look up the parent. I'm hoping my issue is me fundamentally not understanding how to use CONNECT BY so a reproduceable dataset isn't needed.

T_MASTER_DAT has all the part information like part ID, part name, revision, current status. T_MASTER_STR is a structure table that links child and parent parts/assemblies.

Thank you for reading!

Please edit the question and include a minimal reproducible example with: the CREATE TABLE and INSERT statements for your sample data; an English (not code) explnation of the logic that you are trying to implement; the issues/errors with your current solution; and the expected output for that sample data. At the moment, you have told us that something is wrong with your query but you have not explained what the inputs to the query are or what a correct answer looks like so it is impossible to answer your question. — MT0
– MT0, Commented Jun 3, 2024 at 21:02

MT0 · Accepted Answer · 2024-06-03 21:16:53Z

it returns null values for the child part.

If you do not want NULL values then do not use a LEFT OUTER JOIN and use an INNER JOIN instead and put the filter on the c table in the START WITH clause:

SELECT DISTINCT
       c.C_ID      AS CHILD_ID,
       c.PART_ID   AS CHILD_PART,
       s.C_ID_1    AS TOP_PARENT_ID,
       d2.PART_ID  AS TOP_PARENT_PART,
       d2.CUR_FLAG AS TOP_PARENT_CUR_FLAG,
       d2.CANCEL   AS TOP_PARENT_CANCEL,
       d2.UNIT,
       d2.OPP_BR_UNIT
FROM   T_MASTER_STR s
       INNER JOIN T_MASTER_DAT c
       ON c.C_ID = s.C_ID_2
       INNER JOIN T_MASTER_DAT d2
       on d2.C_ID = s.C_ID_1
WHERE  d2.OPP_BR_UNIT is not null
and    d2.CUR_FLAG = 'y'
and    d2.CANCEL = 'n'
CONNECT BY PRIOR s.C_ID_1 = s.C_ID_2
START WITH c.C_ID = 1124609156

If you want to get the root of the hierarchy then use CONNECT_BY_ROOT and if you want to get the leaves of the hierarchy then use WHERE CONNECT_BY_ISLEAF = 1.

You haven't given any sample data or told us what the expected output should be but maybe something like:

SELECT DISTINCT
       CONNECT_BY_ROOT c.C_ID    AS ROOT_CHILD_ID,
       CONNECT_BY_ROOT c.PART_ID AS ROOT_CHILD_PART,
       c.C_ID                    AS LEAF_CHILD_ID,
       c.PART_ID                 AS LEAF_CHILD_PART,
       s.C_ID_1                  AS LEAF_ID,
       d2.PART_ID                AS LEAF_PART,
       d2.CUR_FLAG               AS LEAF_CUR_FLAG,
       d2.CANCEL                 AS LEAF_CANCEL,
       d2.UNIT,
       d2.OPP_BR_UNIT
FROM   T_MASTER_STR s
       INNER JOIN T_MASTER_DAT c
       ON c.C_ID = s.C_ID_2
       INNER JOIN T_MASTER_DAT d2
       on d2.C_ID = s.C_ID_1
WHERE  d2.OPP_BR_UNIT is not null
AND    d2.CUR_FLAG = 'y'
AND    d2.CANCEL = 'n'
AND    CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR s.C_ID_1 = s.C_ID_2
START WITH c.C_ID = 1124609156

This is the solution. Thanks @MT0. Your first query pulls the entire hierarchy and the second pulls the root and the top level which is exactly what I need. It runs quickly too. Thank you for your help! If I had enough rep I'd upvote your answer.

Collectives™ on Stack Overflow

Oracle Connect By hierarchy query returns top level parent but child part is null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related