-
insert into mwebInvoiceDetail (INVDET_ID, InvDet_Inv_ID, InvDet_Code, InvDet_Rate,
InvDet_Work_ID, InvDet_Description, InvDet_Amount, InvDet_Total)
Select mwebInvoiceDetail_SEQ.NextVal, inInv_ID, InvItem_Code, InvItem_Rate,
InvItem_Work_ID, InvItem_Work_Description, sum(InvItem_Amount),
sum(InvItem_Total)
from mwebInvoiceItem
where InvItem_Inv_ID = inInv_ID
Group by InvItem_Inv_ID, InvItem_Code, InvItem_Rate, InvItem_Work_ID,
InvItem_Work_Description;
This insert statement fails giving error ORA - 02287 : sequence number not allowed here.
Why ? What's wrong if I have a Sum(XX) , group by and insert statement with sequence.
Do I have to have cursor to get this to work or is there any other way to do this ?
Thanks
Sonali
Sonali
-
You have to wait until *after* the GROUP BY to pull the sequence.
try this:
INSERT INTO
---mwebInvoiceDetail
---(INVDET_ID, InvDet_Inv_ID, InvDet_Code, InvDet_Rate,
---InvDet_Work_ID, InvDet_Description, InvDet_Amount, InvDet_Total)
SELECT
---mwebInvoiceDetail_SEQ.NextVal,
---T1.*
FROM
---(
---SELECT
------inInv_ID, InvItem_Code, InvItem_Rate,
------InvItem_Work_ID, InvItem_Work_Description, sum(InvItem_Amount),
------sum(InvItem_Total)
---FROM
------mwebInvoiceItem
---WHERE
------InvItem_Inv_ID = inInv_ID
---GROUP BY
------InvItem_Inv_ID, InvItem_Code, InvItem_Rate, InvItem_Work_ID,
------InvItem_Work_Description
---) T1;
HTH,
- Chris
-
Great Chris, thanks a lot !!!!
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|