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.

sql statement

gonzromanMar 30 2010 — edited Apr 1 2010

I would like to create records based on column FROM/TO number using a SQL statement.

Below is a sample table:

Table A has 2 columns (col1 and col2 as number).

COL1 value=1 and COL2 value=5.

I want to insert 5 records in a table from the value of col1 and col2 using SQL. I can do it in PL/SQL but I can't in a single SQL statement. Below is the sample # of records that should be created in the table.

rec1
rec2
rec3
rec4
rec5

Appreciate any help.

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 Apr 29 2010

Added on Mar 30 2010

12 comments

1,801 views

SQL & PL/SQL

sql statement

Comments

Post Details