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.

Complex Update SQL

780828Aug 4 2010 — edited Aug 9 2010
Oracle Version: 10g

I am trying to write an update statement based on chain of items. The table has approximately 2M records. Any help constructing this SQL is appreciated. Thanks!

The items in the table will not be in order. Just use this as sample..

ItemNo1 changed to ItemNo2
ItemNo4 changed to ItemNo3
ItemNo5 changed to ItemNo2
ItemNo6 changed to ItemNo7
ItemNo7 changed to ItemNo8
ItemNo2 changed to ItemNo3

Records 1, 2, 3, 6 should be updated as Chain1 and Records 4 and 5 should be updated as Chain2.

Please see the scripts and sample data below. 
  CREATE TABLE "AVLTSB_SUB" 
   (	"CHAIN_ID" NUMBER NOT NULL ENABLE, 
	"ITM_NO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"SUB_ITM_NO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"UPD_TS" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
	"ORD_IND" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"EFF_DT" DATE NOT NULL ENABLE, 
	"ONE_WY_IND" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE
   ) 


Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-120','009009PM-121',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-121','009009PM-122',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),0);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-123','009009PM-122',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-124','009009PM-125',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-125','009009PM-126',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-126','009009PM-127',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),0);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-127','009009PM-128',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-128','009009PM-129',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-130','009009PM-131',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),0);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-131','009009PM-132',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-132','009009PM-133',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'009009PM-134','009009PM-135',to_timestamp('28-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('28-JUL-10','DD-MON-RR'),1);
Insert into AVLTSB_SUB (CHAIN_ID,ITM_NO,SUB_ITM_NO,UPD_TS,EFF_DT,ONE_WY_IND) values (0,'0030033-8602','0030033-8604',to_timestamp('22-JUL-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_date('22-JUL-10','DD-MON-RR'),1);

Expected Output after updating the Chain_ID:

select chain_id, itm_no, sub_itm_no from avltsb_sub

1	009009PM-120	009009PM-121
1	009009PM-121	009009PM-122
1	009009PM-123	009009PM-122
2	009009PM-124	009009PM-125
2	009009PM-125	009009PM-126
2	009009PM-126	009009PM-127
2	009009PM-127	009009PM-128
2	009009PM-128	009009PM-129
4	009009PM-130	009009PM-131
4	009009PM-131	009009PM-132
4	009009PM-132	009009PM-133
5	009009PM-134	009009PM-135
6	0030033-8602	0030033-8604
This post has been answered by Aketi Jyuuzou on Aug 4 2010
Jump to Answer

Comments

Frank Kulash
Answer
Hi,

This does what you requested: 
SELECT	ord_nbr, step_nbr, area_code
,	SUM (new_grp) OVER ( PARTITION BY  ord_nbr
	    	      	     ORDER BY  	   step_nbr
			   ) AS seq_nbr
FROM	(
	    SELECT  ord_nbr, step_nbr, area_code
	    ,	    CASE
			 WHEN  area_code = LAG (area_code) OVER ( PARTITION BY  ord_nbr
			       		       		   	  ORDER BY  	step_nbr
								)
			 THEN  0
			 ELSE  1
	    	    END		AS new_grp
	    FROM    ords
	)
ORDER BY  ord_nbr
,     	  step_nbr
;
I'm guessing at what role ord_nbr plays in this problem.
user11033437 wrote:
I have the following sample table and data:
CREATE TABLE ords
(	ord_nbr		NUMBER	NOT NULL
,	step_nbr	NUMBER
,	area_code	VARCHAR2(2)
	CONSTRAINT caln_pk PRIMARY KEY (ord_nbr)
);
Did part of your PRIMARY KEY constraint get lost? It looks like ord_nbr is not distinct, so it can't be a primary key by itself.
... I'm wondering if there's I need to turn this into a hierarchical query somehow, so that there's some relationship between step 1 and step 2, step 2 and step 3, etc.

I've played around with ROW_NUMBER, RANK, and DENSE_RANK, but I can't seem to figure out how to add that SEQ_NBR column...
You have some very good instincts!
I think you could do this as a CONNECT BY query, but I don't think it would be any simpler or more efficient. It's good to keep CONNECT BY in mind, and recognize that CONNECT BY is a potential solution for this kind of problem.
This sure looks like a DENSE_RANK problem. That's what I trid first, but I couldn't find a solution simpler than the one above.

Thanks for posting the CREATE TABLE and INSERT statements. That's very helpful.
If you're using Oracle 8.1, that's very significant. You should mention your version in the body of the message, and not just hope that someone notices "8i" in the subject line or tags.

Edited by: Frank Kulash on Feb 18, 2010 2:22 PM
Marked as Answer by 696240 · Sep 27 2020
696240
Yes, my primary key was supposed to include both ord_nbr and step_nbr (oops).

Let me give your query a shot... I just looked up the lag function (never used it before), and I think that's exactly what I needed. I'll reply again and award points once I have confirmed this.

Thanks!

Edit: I will be sure to mention the 8i bit in the body in the future... I guess so far I've had good luck that people have always noticed it in my subject. =)

Edited by: user11033437 on Feb 18, 2010 12:22 PM
Frank Kulash
Hi,

This CONNECT BY solution is much simpler than I would have expected.
Unfortunately, it uses SYS_CONNECT_BY_PATH, which was introduced in Oracle 9, so it won't help you. I just posted it in case others are interested. 
SELECT	ord_nbr, step_nbr, area_code
,	LENGTH ( TRANSLATE ( SYS_CONNECT_BY_PATH ( CASE
							WHEN  area_code = PRIOR area_code
				   			THEN  0
				   			ELSE  1
			      			    END
			    			  , '+'
			    			  )
			   , '10+'
			   , '1'       -- remove all 0s and +s
			   )
		)		AS seq_nbr
FROM	  ords
START WITH	step_nbr	= 1
CONNECT BY	step_nbr	= PRIOR step_nbr + 1
ORDER BY  ord_nbr
,     	  step_nbr
;
696240
Thanks, the LAG function worked great for this. I can think of a number of times where, had I known about LAG, it would have been really useful. I saw there's a related function called LEAD that I'll probably also find to be very useful.

As always, thanks very much!
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 6 2010
Added on Aug 4 2010
15 comments
4,920 views