ERROR group by and sequence

[sonaliak]

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

[chrisrlong]

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

[sonaliak]

Great Chris, thanks a lot !!!!