Hello,
I'm trying to output the data correctly using START WITH and Connect BY with no success. I'm not sure if this desired output below is possible.
If someone can help me with this that would be great.
CREATE TABLE PN_MASTER (
PN VARCHAR2(140),
PN_DESCRIPTION VARCHAR2(1200)
);
CREATE TABLE PN_NEXT_LOWER_ASSEMBLY (
NHA_PN VARCHAR2(140),
NLA_PN VARCHAR2(140),
QTY NUMBER(22),
NLA_POSITION VARCHAR2(80)
);
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_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');
Desired Output
P/N: 201590002-020, DESCRIPTION: MLG RETRACT ACTUATOR
P/N: 114193240, QTY: 1, POS: ONLY, DESCRIPTION: MLG RETRACT ACTUATOR
P/N: 201590906, QTY: 1, POS: ONLY, DESCRIPTION: CYLINDER
P/N: 114193604, QTY: 1, POS: ONLY, DESCRIPTION: MLG ACTR PISTON NUT
P/N: 114193642, QTY: 1, POS: ONLY, DESCRIPTION: MLG RETRCT ACTR GLAND
P/N: 201590205, QTY: 1, POS: ONLY, MLG RETRT ACT ROD
P/N: 114193601, QTY: 1, POS: ONLY, DESCRIPTION: MLG RETRT ACT ROD
P/N: 201590900, QTY: 1, POS: ONLY, DESCRIPTION: RETRACT ACT OTBD PIN
P/N: 201590901, QTY: 1, POS: ONLY, DESCRIPTION: RETRACT ACT INBD PIN
P/N: 201590909, QTY: 1, POS: ONLY, MLG RETRCT ACTR PISTON
Sql Query
SELECT LPAD (' ', 3 * (LEVEL - 1))||'P/N: '||A.NLA_PN||', QTY: '||A.QTY||', POS: '||A.NLA_POSITION||', DESCRIPTION: '||M.PN_DESCRIPTION, LEVEL, ROWNUM
FROM PN_NEXT_LOWER_ASSEMBLY A INNER JOIN PN_MASTER M ON M.PN = A.NHA_PN
START WITH A.NHA_PN = '201590002-020' CONNECT BY A.NHA_PN = PRIOR A.NLA_PN;