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.

Cummulative

user11936261Jan 15 2010 — edited Jan 21 2010

Hi ,

I have a requirement where i have a table

<pre>
itemno ssitem value
IA1 IB1 5
IB1 IC1 2
IC1 NULL 1
ID1 NULL 3
IF1 IK1 5
IK1 NULL 1

</pre>

I need the cummulative values ,here itemno and ssitem has parent child relation .

For IC1(itemno) chain is like (IC1(itemno)>IC1(ssitem)>IB1(itemno))>IB1(SSITEM)>IA1(itemno)

so we have add all the values and show the value for IC1(itemno) as 8

For ID1(itemno) chain is not there so we will show just the corresponding value i.e 3

For Ik1(itemno) chain is (IK1(ssitem)>IF1(itemno)) so some of two records so 6

<pre>
itemno ssitem value cummulative
IA1 IB1 5 NULL
IB1 IC1 2 NULL
IC1 NULL 1 8
ID1 NULL 3 3
IF1 IK1 5 NULL
IK1 NULL 1 6

</pre>

Please help me out

Thanks

This post has been answered by Karthick2003 on Jan 15 2010

Jump to Answer

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.

Locked on Feb 18 2010

Added on Jan 15 2010

4 comments

1,234 views

SQL & PL/SQL

Cummulative

Comments

Post Details