SQL & PL/SQL

Hi Guys,

Please advise.

I thought that the insert all will execute for all the into statements which is correct and i have tested it.

I am facing the following problem.

I am getting the correct results in the following select statement.

get_nextsequence is a function that will return nextval or currval depending upon the seq value.

select get_nextsequence(seq) t1_key_value,id1,name,dob,id2,address,id3,phone,seq from
(
SELECT m1.id id1,
name,
dob,
m2.id id2,
address,
m4.id id3,
m4.phone,
row_number() over(PARTITION BY m1.id ORDER BY m1.id) AS seq
FROM m1,
m2,
m3 m4
WHERE m1.id = m2.id(+)
AND m2.id = m4.id(+)
order by m1.id
)

When i use the following statement as part of the insert all i am not getting the desired results.


INSERT ALL
WHEN(seq = 1) THEN
INTO t1(t1_key, name, dob)
VALUES(t1_key_value, name, dob)
WHEN (SEQ=1 AND id2 is not null) THEN
INTO t2(t1_key, address)
VALUES(t1_key_value, address)
WHEN (SEQ=1 AND id3 is not null) THEN
INTO t3(t1_key, t3_key, phone)
VALUES(t1_key_value, t3_key_seq.nextval, phone)
ELSE
INTO t3(t1_key, t3_key, phone)
VALUES(t1_key_value, t3_key_seq.nextval, phone)

select get_nextsequence(seq) t1_key_value,id1,name,dob,id2,address,id3,phone,seq from
(
SELECT m1.id id1,
name,
dob,
m2.id id2,
address,
m4.id id3,
m4.phone,
row_number() over(PARTITION BY m1.id ORDER BY m1.id) AS seq
FROM m1,
m2,
m3 m4
WHERE m1.id = m2.id(+)
AND m2.id = m4.id(+)
order by m1.id
)

t1 ---> t2 (one to one)
t2 ---> t3 (one to many)


t1_key_value is 1,4,7 in t1 table
t1_key_value is 2,5,8 in t2 table
t1_key_value is 3,6,9 in t3 table

I have solved the problem by creating a table for the result set and then issuing a insert all.

Please advise if there is any way to avoid creating a table.

I have tried this solution to avoid creating a table

INSERT
WHEN(seq = 1) THEN
INTO t1(t1_key, name, dob)
VALUES(t1_key_seq.nextval, name, dob)
WHEN (SEQ=1 AND id2 is not null) THEN
INTO t2(t1_key, address)
VALUES(t1_key_seq.nextval, address)
WHEN (SEQ=1 AND id3 is not null) THEN
INTO t3(t1_key, t3_key, phone)
VALUES(t1_key_seq.nextval, t3_key_seq.nextval, phone)
ELSE
INTO t3(t1_key, t3_key, phone)
VALUES(t1_key_seq.nextval - seq + 1, t3_key_seq.nextval, phone)

select id1,name,dob,id2,address,id3,phone,seq from
(
SELECT m1.id id1,
name,
dob,
m2.id id2,
address,
m4.id id3,
m4.phone,
row_number() over(PARTITION BY m1.id ORDER BY m1.id) AS seq
FROM m1,
m2,
m3 m4
WHERE m1.id = m2.id(+)
AND m2.id = m4.id(+)
order by m1.id,seq
)

The problem with this one is that i am skipping one number when the else statement is executed.

Please advise if there is another way to approach it and its fast. I need to implement this as part of data migration.

Message was edited by:
alvinder