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;