Home » SQL & PL/SQL » SQL & PL/SQL » sql script (9i)
icon9.gif  sql script [message #414615] Wed, 22 July 2009 23:37 Go to next message
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 #414616 is a reply to message #414615] Wed, 22 July 2009 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: sql script [message #414617 is a reply to message #414615] Thu, 23 July 2009 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is your problem writing this query?
Use SQL*Plus and copy and paste what you already tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: sql script [message #414625 is a reply to message #414617] Thu, 23 July 2009 01:03 Go to previous messageGo to next message
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 #414626 is a reply to message #414625] Thu, 23 July 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why a row generator?

Regards
Michel
Re: sql script [message #414634 is a reply to message #414626] Thu, 23 July 2009 01:24 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Why a row generator?

What else we can use except a row generator?
Re: sql script [message #414637 is a reply to message #414626] Thu, 23 July 2009 01:30 Go to previous messageGo to next message
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 #414639 is a reply to message #414637] Thu, 23 July 2009 01:32 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Ofcourse there may be other method

what other methods you are referring to?
Please give hints about other methods
Re: sql script [message #414640 is a reply to message #414637] Thu, 23 July 2009 01:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: sql script [message #414666 is a reply to message #414649] Thu, 23 July 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
His temp table need to be filled from start serial number to end serial number.

It is already filled, read again "Temp table contains srl_no and id".

Quote:
I would be really curious to know on inserting these rows using between clause.

You don't need to insert anything the question is: "fill all the relevant spo no in the temp table." No Insert, only update.

Regards
Michel
Re: sql script [message #414671 is a reply to message #414666] Thu, 23 July 2009 02:47 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
You are correct Michel. I kind of read the message incorrectly. OP just needs to bring spo_no in temp table which is an update not an insert.

[Updated on: Thu, 23 July 2009 02:49]

Report message to a moderator

Previous Topic: getting the date difference in minutes
Next Topic: Mutating Error In Trigger
Goto Forum:
  


Current Time: Mon Nov 11 02:25:00 CST 2024