PL/SQL:insert behaves different in procedure and interactive
Date: Thu, 02 Nov 2000 17:40:57 +0100
Message-ID: <3A019919.131851E_at_tool42.com>
Hi,
[Quoted] I want to store the 100 most ordered items by a particular customer
(120) in octobre.
I can do the following:
INSERT INTO topten
SELECT * FROM
( SELECT TO_DATE('2000-10-31 16:00:00','YYYY-MM-DD HH24:MI:SS'), order.item_no, COUNT(*) FROM order WHERE order.customer_id = 120 AND order.order_date >= TO_DATE('2000-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND order.order_date < TO_DATE('2000-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS') GROUP BY order.item_no ORDER BY 3 DESC )
WHERE rownum < 101;
But this results in very, very long execution time and I don't know why because the select by itself is fast.
Then I tried to first store all rows ordered into another table
INSERT INTO tmp_topten
SELECT TO_DATE('2000-10-31 16:00:00','YYYY-MM-DD HH24:MI:SS'),
order.item_no, COUNT(*)
FROM order
WHERE order.customer_id = 120
AND order.order_date >= TO_DATE('2000-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND order.order_date < TO_DATE('2000-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
GROUP BY order.item_no
ORDER BY 3 DESC;
This is fast. The only differnce is that there is no embracing SELECT *
FROM () WHERE rownum < 11.
This I do in a second step:
INSERT INTO topten
SELECT * FROM tmp_topten WHERE rownum < 101; ORDER BY rownum;
This is also fast and the workaround is complete.
Then there was the next handicap.
I tried all this interactivly in SQL+ and it worked fine.
Then I put this (first and second step) into a stored procedure but
couldn't compile it.
And it looked like that there is someting wrong with the ORDER BY in the
INSERT clause.
It seems like there is no order by clause allowed in a stored procedure
together with INSERT.
I thank everybody who reads until this point. And I appreciate every
help because I feel like
running agoinst walls.
Greetings
Carsten
Received on Thu Nov 02 2000 - 17:40:57 CET