Home » SQL & PL/SQL » SQL & PL/SQL » insert record and increment date
insert record and increment date [message #217285] Thu, 01 February 2007 08:37 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

I have following record structure

PLAN_LEVEL	PERIOD_NUM	DAYS	OPERATIONSEQ	GROUP_ID	ALLOY	PLANNER_CODE	DEPARTMENT_CLASS	DEPARTMENT	LOAD_HOUR	OFFSET_DATE	OFFSET
0	1	20	150	525150	INCONEL alloy 625	WIRE ROD	CD	WP	8.45402793	3/19/2007	14
0	1	20	140	525150	INCONEL alloy 625	WIRE ROD	CD	BA	4.586287974	3/17/2007	30
0	1	20	90	525150	INCONEL alloy 625	WIRE ROD	MM	MW2	0.750050215	3/12/2007	51
0	1	20	70	525150	INCONEL alloy 625	WIRE ROD	MM	M2M	0.027779638	3/12/2007	72
1	1	20	160	525150	INCONEL alloy 625	WIRE ROD	PMD	DPP	1.453799581	3/4/2007	101
1	1	20	140	525150	INCONEL alloy 625	WIRE ROD	CHP	CHP	0.97472359	3/2/2007	132
1	1	20	130	525150	INCONEL alloy 625	WIRE ROD	CHP	SG	4.873617025	2/25/2007	168
1	1	20	120	525150	INCONEL alloy 625	WIRE ROD	CHP	CHP	1.091515669	2/24/2007	205
1	1	20	110	525150	INCONEL alloy 625	WIRE ROD	PMD	PM	0.210131401	2/24/2007	242
1	1	20	90	525150	INCONEL alloy 625	WIRE ROD	PMD	PM	0.262927179	2/18/2007	285
1	1	20	60	525150	INCONEL alloy 625	WIRE ROD	B30	ESR	15.18807308	2/15/2007	331
1	1	20	20	525150	INCONEL alloy 625	WIRE ROD	VIM	VIM	2.191847409	2/12/2007	380



there is days column in the table i just need to replicate/insert above data into same table incrementing offset_date.

e.g.

as days value is 20 i have to insert above record group 20 times and increment offset_date by 1 every time so...

for I 1..days here is 20
insert table 
offset_date+1 for each record value in this case 12 record

so total insert for this record group will be 12*20 and for each data set it should increase offset_date+1

is there any low cost ( good performance )solution than looping through it.

thanks in advance

Re: insert record and increment date [message #217309 is a reply to message #217285] Thu, 01 February 2007 11:02 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Wow so for the data provided, you need to create 525,150 rows for each of those existing columns ?

Is this a one-time run or is it going to ongoing ?

Re: insert record and increment date [message #217311 is a reply to message #217285] Thu, 01 February 2007 11:16 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
it will be ongoing...it is a budgeting from routing operating in Oracle BOM. but it will be quarterly once.
Re: insert record and increment date [message #217312 is a reply to message #217285] Thu, 01 February 2007 11:30 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

insert into foo
select col1,col2,...,offset_date+lv
from foo,(select level lv from dual
connect by level < 21 )
Re: insert record and increment date [message #217315 is a reply to message #217285] Thu, 01 February 2007 11:54 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
thanks srini

i got it

SELECT PLAN_LEVEL,
P_PERIOD_NUM,
US.rn,
DATA_SET_NAME,
DAYS,
OPERATIONSEQ,
GROUP_ID,
ALLOY,
PLANNER_CODE,
DEPARTMENT_CLASS,
DEPARTMENT,
LOAD_HOUR,
OFFSET_DATE + US.rn - 1,
OFFSET,
- 1,
SYSDATE,
- 1,
SYSDATE
FROM SMCBOM.SMCBOM_LH_DATE_TEMP,
(select rownum rn
from user_objects
where rownum <= (select max(days)
from SMCBOM.SMCBOM_LH_DATE_TEMP
WHERE PERIOD_NUM = P_PERIOD_NUM
AND IDENTIFIER = 1)
) US
WHERE PERIOD_NUM = P_PERIOD_NUM
Previous Topic: dbms_metadata.get_ddl introduces a newline in the ddl
Next Topic: Duration between two times
Goto Forum:
  


Current Time: Thu Dec 08 22:31:11 CST 2016

Total time taken to generate the page: 0.12859 seconds