| insert into nested table from subquery [message #210137] |
Tue, 19 December 2006 09:51  |
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 #210234 is a reply to message #210191] |
Tue, 19 December 2006 23:57  |
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 |
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
