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!

oracle hierarchical query

1057922Jan 8 2014 — edited Jan 8 2014

hello all,

this is my data which is in my table.

CREATE TABLE PRMATDTL

(

COMPCODE VARCHAR2(3 BYTE) NOT NULL,

DOCNUMBR VARCHAR2(8 BYTE) NOT NULL,

CONTNUMB VARCHAR2(8 BYTE) NOT NULL,

DOCTDATE DATE NOT NULL,

CHGETYPE VARCHAR2(3 BYTE) NOT NULL,

PARENTNM VARCHAR2(10 BYTE) NOT NULL,

CHILDNAM VARCHAR2(10 BYTE) NOT NULL,

ITEMTYPE VARCHAR2(3 BYTE),

BOQUITEM VARCHAR2(10 BYTE),

LUOMCODE VARCHAR2(10 BYTE),

QUANTITY VARCHAR2(1 BYTE),

QTYPAMTR NUMBER,

WASTEPER NUMBER,

UNITRATE NUMBER,

CREATEDT DATE NOT NULL,

CREATEBY VARCHAR2(6 BYTE) NOT NULL,

OPRSTAMP VARCHAR2(6 BYTE) NOT NULL,

TIMSTAMP DATE NOT NULL,

APPORVBY VARCHAR2(6 BYTE),

APPROVDT DATE,

ACTVCODE VARCHAR2(10 BYTE)

)

SET DEFINE OFF;

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'0200000000', '0205001000', 'BOQ', '1', 'NOS',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:23:58', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:23:58', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'0205001000', 'AC100377', 'ACT', NULL, 'NOS',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:24:05', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:05', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'0205001000', 'AC104307', 'ACT', NULL, 'ROL',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:24:08', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:08', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC100377', '08000007', 'ITE', NULL, 'NOS',

'3', NULL, 5, 20, TO_DATE('01/08/2014 09:24:22', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:22', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC100377', '08000007', 'ITR', NULL, 'NOS',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:24:22', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:22', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC104307', '04600034', 'ITE', NULL, 'ROL',

'2', NULL, 2, 40, TO_DATE('01/08/2014 09:24:36', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:36', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC104307', '04600034', 'ITR', NULL, 'ROL',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:24:36', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:36', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'0200000000', '0201009000', 'BOQ', '10', 'ITM',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:24:48', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:24:48', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'0201009000', 'AC104903', 'ACT', NULL, 'NOS',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC104903', '00800090', 'ITE', NULL, 'COT',

'1', NULL, 5, 60, TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ADD',

'AC104903', '00800090', 'ITR', NULL, 'COT',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:25:07', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

Insert into PRMATDTL

(COMPCODE, DOCNUMBR, CONTNUMB, DOCTDATE, CHGETYPE,

PARENTNM, CHILDNAM, ITEMTYPE, BOQUITEM, LUOMCODE,

QUANTITY, QTYPAMTR, WASTEPER, UNITRATE, CREATEDT,

CREATEBY, OPRSTAMP, TIMSTAMP, APPORVBY, APPROVDT,

ACTVCODE)

Values

('100', 'CO130028', 'CM130124', TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DEL',

'0100000000', '0101001000', 'BOQ', '1', 'ITM',

NULL, NULL, NULL, NULL, TO_DATE('01/08/2014 09:35:10', 'MM/DD/YYYY HH24:MI:SS'),

'SYSTEM', 'SYSTEM', TO_DATE('01/08/2014 09:35:10', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL,

NULL);

COMMIT;

COMPCODE	DOCNUMBR	PARENTNM	CHILDNAM	ITEMTYPE	LUOMCODE	QUANTITY	QTYPAMTR	WASTEPER	UNITRATE
100	CO130028	200000000	205001000	BOQ	NOS
100	CO130028	205001000	AC100377	ACT	NOS
100	CO130028	205001000	AC104307	ACT	ROL
100	CO130028	AC100377	8000007	ITE	NOS	3	5	20
100	CO130028	AC100377	8000007	ITR	NOS
100	CO130028	AC104307	4600034	ITE	ROL	2	2	40
100	CO130028	AC104307	4600034	ITR	ROL
100	CO130028	200000000	201009000	BOQ	ITM
100	CO130028	201009000	AC104903	ACT	NOS
100	CO130028	AC104903	800090	ITE	COT	1	5	60
100	CO130028	AC104903	800090	ITR	COT

I want to show the data like this

COMPCODE	DOCNUMBR	LOCATION	BOQITEM	UNIT	ACTVCODE	UNIT_1	ITEMCODE	UNIT_2	QUANTITY	QTYPAMTR	WASTEPER	UNITRATE
100	CO130028	200000000	205001000	NOS	AC100377	NOS	8000007	NOS
100	CO130028	200000000	205001000	NOS	AC100377	NOS	8000007	NOS	3	5	20
100	CO130028	200000000	205001000	NOS	AC104307	ROL	4600034	ROL
100	CO130028	200000000	205001000	NOS	AC104307	ROL	4600034	ROL	2	2	40
100	CO130028	200000000	201009000	ITM	AC104903	NOS	800090	COT
100	CO130028	200000000	201009000	ITM	AC104903	NOS	800090	COT	1	5	60

I used one self inner join query

SELECT

A1.COMPCODE,

A1.DOCNUMBR,

A1.ITEMTYPE,

A1.LUOMCODE UNIT,

A1.PARENTNM LOCATION,

A1.CHILDNAM BOQITEM,

A2.CHILDNAM ACTVCODE,

A2.LUOMCODE UNIT,

A3.CHILDNAM ITEMCODE,

A3.LUOMCODE UNIT,

A3.QUANTITY,

A3.QTYPAMTR,

A3.WASTEPER,

A3. UNITRATE

FROM

PRMATDTL A1,

PRMATDTL A2,

PRMATDTL A3

WHERE

A1.CHILDNAM=A2.PARENTNM

AND A2.CHILDNAM=A3.PARENTNM

AND SUBSTR(A1.PARENTNM,3,10) = '00000000'

AND A1.DOCNUMBR='CO130028'

AND A2.DOCNUMBR='CO130028'

AND A3.DOCNUMBR='CO130028';

But it seems very slow and very complicated ,I want another one query using with connect by prior ..

Please help me.

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 5 2014

Added on Jan 8 2014

2 comments

225 views