Home » SQL & PL/SQL » SQL & PL/SQL » Nested Loop
Nested Loop [message #309471] Thu, 27 March 2008 11:03 Go to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
Hi,
I have following data:
Order type: Invoice, Sales,Brokerage-Shipper
Sales Terms: Calc Dlvd, Calc FOB,
Pay Terms: 21Days, 10days, FMS 10 Days,
Fill Type: Inventory Purchase, Inventory Grower,
Inventory Pool, Non-Inventory Purchase,
Non-Inventory Grower.
Line Type: Normal, Brokerage, Misc

i have to insert all combinations of this data into table. I think loops will work , but I don't know exactly how to do it.
Can anyone please help.
Re: Nested Loop [message #309474 is a reply to message #309471] Thu, 27 March 2008 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.

For this 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Nested Loop [message #309477 is a reply to message #309474] Thu, 27 March 2008 11:45 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
i ve uploaded file of the data i want in my table
Re: Nested Loop [message #309478 is a reply to message #309477] Thu, 27 March 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post a test case: create table and insert statements.
Also post the result you want with these data.

Regards
Michel

Re: Nested Loop [message #309479 is a reply to message #309478] Thu, 27 March 2008 12:10 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
CREATE TABLE ORDER_TYPE_DIM (
OrderTypeKey number(5) Primary Key,
OrderType varchar2(10)Not Null,
SalesTerms varchar2(30)Not Null,
PayTerms varchar2(20)Not Null,
FillType varchar2(20)Not Null,
LineType varchar2(10)Not Null
) tablespace user_data


insert into order_type_dim
values(1,'Sales','Calc FOB','FMS 10 Days','Inventory Purchase','Normal');

I ve already uploaded data in text file, is it ok, or in what formatting should i send it

Thanks,
Re: Nested Loop [message #309486 is a reply to message #309479] Thu, 27 March 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I ve already uploaded data in text file, is it ok,

No, insert statements are required as long as with the result you expect for these data.
And format them.
And gives you Oracle version (4 decimals).

Regards
Michel

[Updated on: Thu, 27 March 2008 12:47]

Report message to a moderator

Re: Nested Loop [message #309497 is a reply to message #309471] Thu, 27 March 2008 13:22 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
I ve formatted my data file more neatly.But I dont know what it means "Oracle version (4 decimal) format".
Re: Nested Loop [message #309499 is a reply to message #309497] Thu, 27 March 2008 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dont know what it means "Oracle version (4 decimal) format".

It is not "Oracle version format", but Oracle version.
I assume you work with Oracle.
I assume you know Oracle delivered many versions of its product.
I assume you work with at least one version.
So give your version number. Knowing this version contains 5 decimals, give the first 4 of them, for instance 10.2.0.3

Now your file is a select output, it is not a list of INSERT statements.
You gave an insert statement in your previous post, so I assume you know what it is.
The purpose for us is to be able to reproduce what you can do.
Then, with the test case you give, we expect you give us the result you want.

Regards
Michel

[Updated on: Thu, 27 March 2008 13:40]

Report message to a moderator

Re: Nested Loop [message #309503 is a reply to message #309499] Thu, 27 March 2008 13:51 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
My Oracle version is 10.2.0.1.0
Re: Nested Loop [message #309504 is a reply to message #309499] Thu, 27 March 2008 13:53 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
Actually I want that data is inserted into the table thru loop using one insert statement , n i dont have to write insert statements as many times as records are. so i gave u sample of my insert statement.
What should I give u more.
Thanks a lot for your help
Re: Nested Loop [message #309506 is a reply to message #309504] Thu, 27 March 2008 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you have? Data in file? Data in table?
What do you want? Insert statements from data from X? Data in another table?
Question

Regards
Michel
Re: Nested Loop [message #309507 is a reply to message #309506] Thu, 27 March 2008 14:02 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
I have data in text file,which I sent u previously as an attachment. And i ve to insert this data into table.

thanks
Re: Nested Loop [message #309509 is a reply to message #309507] Thu, 27 March 2008 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now it is clear.
Use SQL*Loader.

Regards
Michel
Re: Nested Loop [message #309511 is a reply to message #309504] Thu, 27 March 2008 14:21 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Newbie to the rescue!

I think I understand what you are looking for:


First, you have to transform the "raw data" into a pseudo table that you can use for your insert, for example:
Order type
-----------------
Invoice
Sales
Brokerage-Shipper

We can convert it into:

Select 'Invoice' OTK
  from dual
Union all
Select 'Sales' OTK
  from dual
Union all
select 'Brokerage-Shipper' OTK
  from dual

In the same way:

Sales Terms
-----------
Calc Dlvd
Calc FOB


Select 'Calc Dlvd' ST
  from dual
Union all
Select 'Calc FOB' ST
  from dual



You can do the same for the other columns.

Then you do the insert using a simple Cartesian product (?) to obtain all the combinations of the data:

Insert into ORDER_TYPE_DIM ( OrderTypeKey, OrderType, SalesTerms, ...)
(Select ROWNUM, OTK_Data.OTK, ST_Data.ST

   from (Select 'Invoice' OTK
           from dual
          Union all
         Select 'Sales' OTK
           from dual
          Union all
         select 'Brokerage-Shipper' OTK
           from dual) OTK_Data,

        (Select 'Calc Dlvd' ST
           from dual
          Union all
         Select 'Calc FOB' ST
           from dual) ST_Data,
         ...
)


Naturally, this is a newbie approach... if you need something more powerful you can substitute the "select ... union all Select ..." idea with a function. (Like the opposite of the concat_all function)

Hope it works!


Edit: Meh... way too late.

[Updated on: Thu, 27 March 2008 14:23]

Report message to a moderator

Re: Nested Loop [message #309516 is a reply to message #309509] Thu, 27 March 2008 14:41 Go to previous messageGo to next message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
ok ,
I appreciate your help.
Re: Nested Loop [message #309530 is a reply to message #309511] Thu, 27 March 2008 15:36 Go to previous message
ambreen.fatima
Messages: 15
Registered: February 2007
Location: PAKISTAN
Junior Member
Thanku Tafer , it works!!!
Previous Topic: getting error in Procedure
Next Topic: Is it possible in SQL or PL/SQL pls advise
Goto Forum:
  


Current Time: Sun Dec 04 23:02:48 CST 2016

Total time taken to generate the page: 0.04468 seconds