sql script [message #414615] |
Wed, 22 July 2009 23:37 |
shaz
Messages: 182 Registered: June 2009
|
Senior Member |
|
|
I have two tables master and temp
master table contains the sp_no, range of srl_no.
Temp table contains srl_no and id, now I have to write a script which will fill all the relevant spo no in the temp table..
that is:
master
sp_no start_srl_no end_srl_no
1001 101 150
1005 151 180
1010 181 215
temp
srl_no spo_no
101 1001(to be filled)
102 1001
103 1001
.
.
.
150 1001
151 1005
152 1005
please suggest.
[EDITED by LF: applied [pre] tags]
[Updated on: Thu, 23 July 2009 00:44] by Moderator Report message to a moderator
|
|
|
|
|
Re: sql script [message #414625 is a reply to message #414617] |
Thu, 23 July 2009 01:03 |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
I cannot provide direct answers as this is against forum guidelines, however, I can give you hints to help you get started. You need to use row generator technique for this. Search this forum for the same
|
|
|
|
|
Re: sql script [message #414637 is a reply to message #414626] |
Thu, 23 July 2009 01:30 |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
I was thinking of generating rows for the maximum difference between end serial no and start serial no. Use this to cartesian join with main table and finally add row_number() over to the start serial no and stopping when this equals end serial number.
Ofcourse there may be other method as well but this one came to mind immediately.
|
|
|
|
Re: sql script [message #414640 is a reply to message #414637] |
Thu, 23 July 2009 01:34 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Unless I misunderstood, he said that "Temp table contains srl_no and id" and he just want "to write a script which will fill all the relevant spo no in the temp table", which row generator is needed to fill a column? Just BETWEEN is needed.
Regards
Michel
[Updated on: Thu, 23 July 2009 01:34] Report message to a moderator
|
|
|
Re: sql script [message #414645 is a reply to message #414639] |
Thu, 23 July 2009 01:46 |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
I just said there may be other methods. may be using plsql pipelined function or model clause. I am not very conversant with model clause so I cannot be sure on that.
|
|
|
Re: sql script [message #414649 is a reply to message #414645] |
Thu, 23 July 2009 01:51 |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
His temp table need to be filled from start serial number to end serial number. In his example in case of sp_no 1001 which has start serial number as 101 and end serial number as 150 he wants to insert seperate rows in the temp table begining at 101 and ending at 150. I would be really curious to know on inserting these rows using between clause.
[Updated on: Thu, 23 July 2009 01:52] Report message to a moderator
|
|
|
|
|