Issue with Subquery/Outer Query with BOM LEVEL - Results Messed Up

Summary:

Hi everyone, I would greatly appreciate any suggestions or help regarding the use of outer queries or subqueries. I want to use multiple subqueries and join them into one query, but I’m encountering an issue.

Content (please ensure you mask any confidential information):

Here is my query:

SELECT 

        LEVEL BOM_LEVEL,

        CONNECT_BY_ISCYCLE IS_CYCLE,

        Q_BOM.COMPONENT_NUM ITEM,

        Q_BOM.COMPONENT_DESCR ITEM_DESCR,

        Q_BOM.QTY,

        Q_BOM.UOM,

        LEVEL - 1 PARENT_SELECTED,

        Q_BOM.ASSEMBLY_NUM PARENT_BOM,

        CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,

        SUBSTR(SYS_CONNECT_BY_PATH(Q_BOM.ASSEMBLY_NUM, ' <-- '), 5) ASSEMBLY_PATH,