Insert/Select statement 1

[jwdcfdeveloper]

Does anyone know how to use a sequential value in an insert/select statement, or a work around. I am testing an insert select statement:

insert into table_a(id, description, price, quantity, discount)
select id_seq.nextval, a.description, b.price, b.quantity
from my_table a, your_table b
where a.id = b.id
and....

Basically the query errors out because Oracle doesn't like sequential values in it's insert/select statements. I open to suggestions as to how I can fix this.

 

[SantaMufasa]

...the query errors out because Oracle doesn't like sequential values in it's insert/select statements.

Not true. (This is a PICNIC problem: Problem In Chair Not In Computer.)

Your INSERT statement specifies five values to insert, but you provide only four values in your SELECT. Since I don't know which table should contribute the "discount", I'll just use a literal value (.10) to show proof of concept:

insert into table_a(id, description, price, quantity, discount)
select id_seq.nextval, a.description, b.price, b.quantity, .10
from my_table a, your_table b
where a.id = b.id;

1 row created.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 00:09 (16Feb05) UTC (aka "GMT" and "Zulu"),
@ 17:09 (15Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jwdcfdeveloper]

Actually that was only an example I forgot to add the fifth field. Here is the actual query:

Insert Into Admin_Finance.Fin_Order_Detail ( Order_Detail_Id, Order_Id, Product_Id,Order_Detail_Qty,
Order_Detail_Price, Order_Detail_Ext_Price, Order_Detail_Date, Product_Uom, Product_Actual_Cost, Invoice_Number)
Select Fin_Order_Detail_Seq.Nextval, 57159, a.product_id,
(nvl(b.pack_qty,1) * Sum(a.quantity)) As Order_Detail_Qty,
((nvl(b.pack_qty,1) * c.status_discount_price)) As order_detail_price,
((nvl(b.pack_qty,1) * Sum(a.quantity) * c.status_discount_price)) As order_detail_ext_price,
TO_DATE('02152005', 'MMDDYYYY'), 'EACH',
((nvl(b.pack_qty,1) * c.status_discount_price)) As Product_Actual_Cost,624437
From Orc_Cart a, Fin_Product b, Fin_Product_Price c
Where a.product_id = b.product_id
And a.product_id = c.product_id
And c.product_price_group >=
(
Select Min(product_price_group)
From Fin_Product_Price
Where product_id = a.product_id
And status_price_group_id = c.status_price_group_id
And To_Char(sysdate,'YYYYMMDD')
Between To_Char(price_active_date,'YYYYMMDD') AND
To_Char(nvl(price_active_end_date,sysdate),'YYYYMMDD')
)
And c.status_price_group_id = 'ACTIVE'
And a.member_id = 8535
Group By a.product_id, a.quantity, b.pack_qty, c.status_discount_price

Everytime I run this query I get the following error:
ORA-02287: sequential number not allowed here.

I read that a potential solution is to use an Inline View to hide the Group By, but when I tried that, the query got so complex and return thousands of rows instead of the 6 I was looking for. So either I need a really good inline view or another alternative. Either way, this query as is doesn't work.

 

[SantaMufasa]

JW,

Thank you for disclosing the full code set...it allows us to provide a solution for you.

The problem is that your SELECT statement allows only GROUP expressions and literals, but your sequence, "Fin_Order_Detail_Seq.Nextval", is neither. Here is a simplified illustration of your problem:

select tempseq.nextval,dept_id from s_emp group by dept_id
               *
ERROR at line 1:
ORA-02287: sequence number not allowed here

Now, to work around your problem just place your GROUP Select into an "in-line view":

select tempseq.nextval,dept_id
from (select dept_id from s_emp group by dept_id);

 NEXTVAL    DEPT_ID
-------- ----------
       2         10
       3         31
       4         32
       5         33
       6         34
       7         35

Let us know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:20 (16Feb05) UTC (aka "GMT" and "Zulu"),
@ 09:20 (16Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jwdcfdeveloper]

It's definitely much closer, but I'm still getting twice as many results as I need. I know I need to tighten up a join somewhere, but I'm not sure where. Here is the revised SELECT portion of the query based upon your recommendations:

Select Fin_Order_Detail_Seq.Nextval, 57159, a.product_id,
(nvl(b.pack_qty,1) * a.quantity) As Order_Detail_Qty,
((nvl(b.pack_qty,1) * c.status_discount_price)) As order_detail_price,
((nvl(b.pack_qty,1) * a.quantity * c.status_discount_price)) As order_detail_ext_price,
TO_DATE('02152005', 'MMDDYYYY'), 'EACH',
((nvl(b.pack_qty,1) * c.status_discount_price)) As Product_Actual_Cost,624437
From Orc_Cart a, Fin_Product b, Fin_Product_Price c,
(Select Sum(quantity) as quantity From Orc_Cart Where member_id = 8535 Group By quantity)
Where a.product_id = b.product_id
And a.product_id = c.product_id
And c.product_price_group >=
(
Select Min(product_price_group)
From Fin_Product_Price
Where product_id = a.product_id
And status_price_group_id = c.status_price_group_id
And To_Char(sysdate,'YYYYMMDD')
Between To_Char(price_active_date,'YYYYMMDD') AND
To_Char(nvl(price_active_end_date,sysdate),'YYYYMMDD')
)
And c.status_price_group_id = 'ACTIVE'
And a.member_id = 8535


In the case of this query I am looking to return 9 rows of data, but got 18. Any suggestions would be appreciated.

Thanks,

JW

 

[SantaMufasa]

Are the "extra" 9 rows duplicates of the "other" 9 rows? (I'm flying a little blind since I don't know your data or expected results.) Is it possible to post the 18 rows here and explain which 9 should go away?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:03 (17Feb05) UTC (aka "GMT" and "Zulu"),
@ 10:03 (17Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jwdcfdeveloper]

I figured out how to eliminate the other 9 rows, I need to add the product_id from the ORC_Cart table to the inline query, and I got the results I needed. Thanks for all of your help SantaMufasa.