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!

Lead and Lag

GmoneyJan 15 2013 — edited Jan 17 2013

Hello all,

I have a query that is reading a table that contains design information for a an electronic circuit. These designs are fluid to the extent the data is not always in the same location in the table (row).
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

create table DSGN_CHK
(
ISSUE VARCHAR2(8)       not null,
DESIGN VARCHAR2(8)      not null,
ROW_SEQ_NBR NUMBER(2) not null,
NOTE_IND NUMBER(2) 	,
EQUIP VARCHAR2(8),
FAC_TYPE VARCHAR2(8),
UNIT VARCHAR2(8),
PATH_TYPE VARCHAR2(4)
)
INSERT INTO DSGN_CHK VALUES ('1','123456','6', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','123456','5', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','123456','4','0','ABC','NULL','182','T');
INSERT INTO DSGN_CHK VALUES ('1','123456','3', NULL,'ABC','NULL','183','R');
INSERT INTO DSGN_CHK VALUES ('1','123456','9', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','123456','10', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','123456','7', NULL,'DEF', NULL,'281','T');
INSERT INTO DSGN_CHK VALUES ('1','123456','8', NULL,'DEF', NULL,'282','R');
INSERT INTO DSGN_CHK VALUES ('2','123456','1', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('2','123456','2', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('2','123456','3','2','ABC','NULL','182','T');
INSERT INTO DSGN_CHK VALUES ('2','123456','4', NULL,'ABC','NULL','183','R');
INSERT INTO DSGN_CHK VALUES ('2','123456','5', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('2','123456','6', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('2','123456','7', NULL,'DEF','22L','281','T');
INSERT INTO DSGN_CHK VALUES ('2','123456','8', NULL,'DEF','22L','282','R');
INSERT INTO DSGN_CHK VALUES ('4','654321','1', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','2', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','3', NULL,'100','TA','9','X1');
INSERT INTO DSGN_CHK VALUES ('4','654321','4', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','5', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','6', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','7', NULL,'107','TB','5','X1');
INSERT INTO DSGN_CHK VALUES ('4','654321','8', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','9', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','10', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','11', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('4','654321','12','2','12','2NL','475','T');
INSERT INTO DSGN_CHK VALUES ('4','654321','13', NULL, NULL, NULL, NULL, NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','1', NULL,'XDT123', NULL, NULL, NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','2', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','3', NULL,'222','GT','66','X1');
INSERT INTO DSGN_CHK VALUES ('1','987654','4', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','5', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','6', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','7','6','14','2NL','36','T');
INSERT INTO DSGN_CHK VALUES ('1','987654','8', NULL,'14','2NL','37','R');
INSERT INTO DSGN_CHK VALUES ('1','987654','9', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','10', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','11', NULL,'XDT123','XYZ5.9','17', NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','12','3','9','NULL','1252','T');
INSERT INTO DSGN_CHK VALUES ('1','987654','13', NULL, NULL, NULL, NULL, NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','14', NULL,'9','NULL','1253','R');
INSERT INTO DSGN_CHK VALUES ('1','987654','15', NULL,'XDT123', NULL, NULL, NULL);
INSERT INTO DSGN_CHK VALUES ('1','987654','16', NULL, NULL, NULL, NULL, NULL);
COMMIT;

This is my desired results:

ISSUE	DESIGN	ROW_SEQ_NBR	UNIT_NM	  FAC_TYPE     PAIR
4	654321	12	                475	     2NL	       T
1	987654	14	                1252-1253	    	     T-R
2	123456	8	                281-282	     22L	     T-R

Here is what I have so far.

WITH T AS 
(
select ISSUE,
       DESIGN, 
       ROW_SEQ_NBR,
	     NOTE_IND,
       FAC_TYPE,
       EQUIP,  
       LEAD(UNIT,1,0) OVER(PARTITION BY ISSUE ORDER BY ROW_SEQ_NBR DESC) || '-' || (UNIT) UNIT_NM,
       LEAD (PATH_TYPE,1,0) OVER (PARTITION BY ISSUE ORDER BY ROW_SEQ_NBR DESC) || '-' ||(PATH_TYPE) PAIR,
       ROW_NUMBER() OVER (partition by ROW_SEQ_NBR ORDER BY ISSUE DESC) RN
from DSGN_CHK 
where DESIGN  in (123456,654321,987654) --987654 --123456 -- = 654321 --
and PATH_TYPE is not null
-- and unit is not null
)
SELECT  ISSUE,
		    DESIGN,
        ROW_SEQ_NBR,
        UNIT_NM,
        FAC_TYPE,
        PAIR
FROM T
WHERE RN = 1

When I run this on production data there are instances where it appears that the UNIT_NM is actually be compared to the previous row in the results table as opposed to the individual record. This makes me think I may have the Lead function in the wrong part of the query. However, I am not 100% sure.

Thanks for looking

Edited by: GMoney on Jan 15, 2013 11:03 AM

Edited by: GMoney on Jan 15, 2013 11:28 AM

Edited by: GMoney on Jan 15, 2013 12:10 PM
Changed ROW_SEQ_NBR and NOTE_IND to NUMBER

This post has been answered by Frank Kulash on Jan 17 2013

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 14 2013

Added on Jan 15 2013

#analytics, #sql

15 comments

468 views