Hi there,

I am using Oracle 9i with TOAD for the first time and hope someone can give me an advice.
My problem:

I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.

In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:

For example:
ID INP1 INP2 INP3 OUT1 OUT2 OUT3
A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C

TO

ID SEQUENCE_NR INPUT OUTPUT
A1 1 CALL A ANSW A
A1 2 CALL B ANSW B
A1 3 CALL C ANSW C

Can someone tell me what is the best way to do it or maybe suggest another solution for my problem?

Thanks a lot,
Fausto

You've got the right idea.

I think you need two tables for all the relationships you'll want though, if the number if ins and out are ever different for example. And you'll probably want to record the type of information that will be sent in and returned...

SPEC_IN(ID,SEQ,NAME,TYPE)
SPEC_OUT(ID,SEQ,NAME,TYPE)

-Chris

Hi Chris, and thanx for your posting.

My main problem is how to copy the rows from the original table to the new table(s). Can you help me there also?

Thanx, Fausto

you'll want 6 queries ( i assume )

insert into SPEC_IN (id,seq,name)
VALUES ( SELECT id,1,INP1 FROM SPECS );
insert into SPEC_IN (id,seq,name)
VALUES ( SELECT id,2,INP2 FROM SPECS );
insert into SPEC_IN (id,seq,name)
VALUES ( SELECT id,3,INP3 FROM SPECS );

And the same for the spec_out table

-Chris
http://www.bitesizeinc.net/index.php/sql.html

Unfortunatelly it does not work. I get the following error message:

ORA-00936: missing expression

But I managed to do what I needed using PL/SQL.

Thanx anyway for you help.

Fausto

The correct syntax is :

insert into SPEC_IN (id,seq,name)
SELECT id,1,INP1 FROM SPECS;