Home » SQL & PL/SQL » SQL & PL/SQL » insert into nested table from subquery
insert into nested table from subquery [message #210137] Tue, 19 December 2006 09:51 Go to next message
ajones@granitenet.com
Messages: 3
Registered: December 2006
Junior Member
Hello I have a table SUB_Collection with a field INV_Collection as a nested table. I am trying to write an insert statement that inserts values into table SUB_COLLECTION including its nested table fields from the results of a subquery. Here is my code:

CREATE OR REPLACE TYPE INV_CHILD AS OBJECT
MACNUM VARCHAR2(8),
INVOICE_NUMBER VARCHAR2(10)
INVOICE_DATE DATE,
CUS_BTN VARCHAR2(15),
CUS_PARENT VARCHAR2 (8));

create or replace type INV_Parent as table of INV_CHILD


CREATE TABLE SUB_COLLECTION(
MACNUM VARCHAR2(8),
ilec VARCHAR2(15)
INV_COLLECTION INV_pARENT
)
NESTED TABLE INV_COLLECTION STORE AS NESTED_TABLE



INSERT INTO SUB_COLLECTION (MACNUM,ILEC,INV_COLLECTION)
(
SELECT CF.MACNUM,CF.CUS_PACKAGE ,CF.MACNUM,HBH.INVOICE_NUMBER, HBH.INVOICE_DATE,CF.CUS_BTN, CF.CUS_PARENT
FROM Cust_File@VD03_ORG CF, HIST_BILLGEN_HEADER HBH
WHERE CF.MACNUM=HBH.MACNUM
AND HBH.Total_Due <> 0
AND HBH.Invoice_Date BETWEEN '31-Mar-2003' AND '31-Jul-2006');


I get the compiler error 'too many values' on line 1.
Thanks in advance for any help on how to write my insert statement.
Re: insert into nested table from subquery [message #210183 is a reply to message #210137] Tue, 19 December 2006 16:13 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


=========

(MACNUM,ILEC,INV_COLLECTION) ==> insert expects 4 columns.

Your select has more than that.

Srini

Re: insert into nested table from subquery [message #210184 is a reply to message #210137] Tue, 19 December 2006 16:15 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Duh ! I forgot how to count.

(MACNUM,ILEC,INV_COLLECTION) ==> they are 3.
Re: insert into nested table from subquery [message #210191 is a reply to message #210137] Tue, 19 December 2006 17:07 Go to previous messageGo to next message
ajones@granitenet.com
Messages: 3
Registered: December 2006
Junior Member
That is because the 3rd column inv_collection is a nested table with 5 fields. I do not know how to write the insert for these fields because it is a nested table.
Re: insert into nested table from subquery [message #210234 is a reply to message #210191] Tue, 19 December 2006 23:57 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Try this... not tested though.

INSERT INTO SUB_COLLECTION (MACNUM,ILEC,INV_COLLECTION)
(
SELECT CF.MACNUM,CF.CUS_PACKAGE ,inv_parent(inv_child(CF.MACNUM,HBH.INVOICE_NUMBER, HBH.INVOICE_DATE,CF.CUS_BTN, CF.CUS_PARENT))
FROM Cust_File@VD03_ORG CF, HIST_BILLGEN_HEADER HBH
WHERE CF.MACNUM=HBH.MACNUM
AND HBH.Total_Due <> 0 
AND HBH.Invoice_Date BETWEEN to_date('31-Mar-2003','dd-Mon-yyyy') AND to_date('31-Jul-2006','dd-Mon-yyyy'));


Also compare dates with dates.

[Updated on: Wed, 20 December 2006 00:02]

Report message to a moderator

Re: insert into nested table from subquery [message #210419 is a reply to message #210137] Wed, 20 December 2006 14:09 Go to previous message
ajones@granitenet.com
Messages: 3
Registered: December 2006
Junior Member
Thanks a million, that worked.
Previous Topic: migrate wrapped package
Next Topic: Counting in a subquery
Goto Forum:
  


Current Time: Sat Dec 10 20:46:42 CST 2016

Total time taken to generate the page: 0.08976 seconds