Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Repeating Data Output With Join

Karl KieslingMar 9 2025 — edited Mar 9 2025

Hello,

I added a LEFT JOIN clause to an existing Query which I was helped with this amazing communiy that is outputting repeatable data.

I added a LEFT JOIN to PN_INTERCHANGLE Table along with PN_MASTER Table witch I'm not sure is correctly setup or if I need to use a different approach.

Below is the desired output with columns missing , tables and query. Your help would be much appreciated.

ROWNUM	LEVEL		pn				pn_interchangeable	
1		1		201590002-020		201590002-020		
2		1		201590002-020		201590002-010		
4		1		201590002-020		201590004			
5		1		201590002-020		201590002			
6		1		201590002-020		201590001			
7		2		114193240		114193240			
8		2		114193240		201697212			
9		2		114193240		201697202			
10		2		114193240		201697201			
11		2		114193240		201590200			
12		2		114193240		201590206			
13		2		114193240		201697214			
14		2		114193240		201697219			
15		3		201590906		201590906			
16		3		201590906		201697610			
17		3		201590906		201697601			
18		3		201590906		55-4517001-00		
19		3		201590906		201697612			
20		3		201590906		201697604			
21		3		201590906		201697609			
22		3		201590906		201697607			
23		3		201590906		114193641			
24		3		201590906		201697600			
25		2		114193604		114193604			
26		2		114193604		201590910			
27		2		114193642		114193642			
28		2		114193642		201590911			
29		2		114193642		201590913			
30		2		201590205		201590205			
31		2		201590205		114193244			
32		2		201590205		201590204			
33		2		201590205		114193247			
34		3		114193601		114193601			
35		3		114193601		55-4517015-00		
36		3		114193601		201590908			
37		2		201590900		201590900			
38		2		201590900		114193637			
39		2		201590900		55-4517017-00		
40		2		201590901		201590901			
41		2		201590901		55-4517016-00		
42		2		201590901		114193610			
43		2		201590909		201590909			
44		2		201590909		11419360

CREATE TABLE PN_MASTER (
  PN VARCHAR2(140),
  PN_DESCRIPTION VARCHAR2(1200)
);

CREATE TABLE PN_INTERCHANGEABLE (
    PN VARCHAR2(140),
    PN_INTERCHANGEABLE VARCHAR2(140),
    INTERCHANGEABLE_TYPE VARCHAR2(4)
);

CREATE TABLE PN_NEXT_LOWER_ASSEMBLY (
    NHA_PN VARCHAR2(140),
    NLA_PN VARCHAR2(140),
    QTY NUMBER(22),
    NLA_POSITION VARCHAR2(80)
);

CREATE TABLE PN_INVENTORY_DETAIL ( 
  NHA_PN VARCHAR2(140) NULL,
  NHA_SN VARCHAR2(140) NULL,
  PN VARCHAR2(140),
  SN VARCHAR2(140)
);

INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('114193240', 'MLG RETRCT ACT CYLINDER');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('114193601',	'NP-RETRACT ACT PISTON');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('114193604',	'MLG ACTR PISTON NUT');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('114193642',	'MLG RETRCT ACTR GLAND');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590002-020',	'MLG RETRACT ACTUATOR');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590205',	'MLG RETRT ACT ROD');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590900',	'RETRACT ACT OTBD PIN');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590901',	'RETRACT ACT INBD PIN');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590906',	'CYLINDER');
INSERT INTO PN_MASTER(PN, PN_DESCRIPTION) VALUES ('201590909',	'MLG RETRCT ACTR PISTON');

INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '114193240', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201590206', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201590200', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201697202', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201697212', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201697201', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201697214', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193240', '201697219', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193601', '114193601', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193601', '201590908', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193601', '55-4517015-00', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193604', '114193604', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193604', '201590910','B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193642', '114193642','M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193642', '201590911','B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('114193642',	'201590913', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590002-020', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590003', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590002-010','B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590002', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590001', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590002-020', '201590004', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590205', '201590205', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590205', '114193244', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590205', '114193247', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590205', '201590204', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590900', '201590900', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590900', '55-4517017-00','B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590900', '114193637', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590901', '201590901', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590901', '114193610', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590901', '55-4517016-00', 'B');	
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201590906', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '114193641', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '55-4517001-00','B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697600', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697601', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697604', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697607', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697610', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590906', '201697612', 'B');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590909', '201590909', 'M');
INSERT INTO PN_INTERCHANGEABLE (PN, PN_INTERCHANGEABLE, INTERCHANGEABLE_TYPE) VALUES ('201590909', '114193603', 'B');

INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('114193240', '201590906', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '201590205', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '201590900', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '114193604', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '114193240', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '114193642', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '201590901', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590002-020', '201590909', 1, 'ONLY');
INSERT INTO PN_NEXT_LOWER_ASSEMBLY(NHA_PN, NLA_PN, QTY, NLA_POSITION) VALUES ('201590205', '114193601', 1, 'ONLY');

INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES (NULL, NULL , '201590002-020', 'B2446'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590205', 'MDS0483J0856'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590900', 'EM29232-3'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590901', 'EM29596-130'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590901', 'EM29596-130'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590909', 'EM29011-38'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201590910', 'EM29432-31'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES('201590002-020', 'B2446', '201590913', 'EM29034-14'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201590002-020', 'B2446', '201697211', 'B9-2'); 
INSERT INTO PN_INVENTORY_DETAIL (NHA_PN, NHA_SN, PN, SN) VALUES ('201697211', 'B9-2', '201697607', 'B9-2');

WITH
    DATA AS (
      SELECT NHA_PN, NLA_PN, QTY, NLA_POSITION
      FROM PN_NEXT_LOWER_ASSEMBLY
      UNION ALL
      SELECT I.NHA_PN, I.PN, 1, 'ONLY'
      FROM PN_INVENTORY_DETAIL I
      WHERE I.NHA_PN IS NULL
    )
    SELECT ROWNUM, LEVEL, A.NLA_PN, C.PN_INTERCHANGEABLE, M.PN_DESCRIPTION, C.INTERCHANGEABLE_TYPE, A.QTY, A.NLA_POSITION 
    FROM DATA A LEFT JOIN PN_MASTER M ON M.PN = A.NLA_PN LEFT JOIN PN_INTERCHANGEABLE C ON C.PN = M.PN
    START WITH NLA_PN = '201590002-020' AND NHA_PN IS NULL CONNECT BY A.NHA_PN = PRIOR A.NLA_PN
    ORDER SIBLINGS BY A.NLA_PN ASC, C.INTERCHANGEABLE_TYPE DESC;
This post has been answered by Paulzip on Mar 9 2025
Jump to Answer
Comments
Post Details
Added on Mar 9 2025
#pl-sql
2 comments
73 views
1 mention