Reputation: 85
I am trying to run the following query to fill a table with the proper values. I have ran the select statement alone, without the price_s and the created_by, creation_date, last_updated_by, and last_update_date, and it pulls everything that I need. When I add those values, it gives errors, I have gotten through some of them, but I am stuck on this one ORA-02287: Sequence number not allowed here
, I have looked everywhere and cannot find what I am doing wrong. It is probably some simple answer, but I just cannot see it right now.
Here is the code:
CREATE SEQUENCE price_s START WITH 1001;
INSERT INTO price (price_id, item_id, price_type, active_flag, start_date, end_date, amount, created_by, creation_date, last_updated_by, last_update_date)
SELECT price_s.nextval, i.item_id,
active_flag,
cl.common_lookup_id,TRUNC (i.release_date),
CASE
WHEN (TRUNC (SYSDATE) - TRUNC (i.release_date)) < 31 AND active_flag = 'N' THEN NULL
WHEN (TRUNC (SYSDATE) - TRUNC (i.release_date)) > 30 AND active_flag = 'N' THEN TRUNC (i.release_date) + 30
END AS END_DATE,
CASE
WHEN (TRUNC (SYSDATE) - TRUNC (i.release_date)) < 31 AND active_flag = 'Y' THEN
CASE
WHEN rental_days = 1 THEN '3'
WHEN rental_days = 3 THEN '10'
WHEN rental_days = 5 THEN '15'
END
WHEN (TRUNC (SYSDATE) - TRUNC (i.release_date)) > 30 AND active_flag = 'N' THEN
CASE
WHEN rental_days = 1 THEN '3'
WHEN rental_days = 3 THEN '10'
WHEN rental_days = 5 THEN '15'
END
WHEN (TRUNC (SYSDATE) - TRUNC (i.release_date)) > 30 AND active_flag = 'Y' THEN
CASE
WHEN rental_days = 1 THEN '1'
WHEN rental_days = 3 THEN '3'
WHEN rental_days = 5 THEN '5'
END
END AS AMOUNT,
1,SYSDATE,1,SYSDATE
FROM item i CROSS JOIN
(SELECT 'Y' AS active_flag FROM dual
UNION ALL
SELECT 'N' AS active_flag FROM dual) af CROSS JOIN
(SELECT '1' AS rental_days FROM dual
UNION ALL
SELECT '3' AS rental_days FROM dual
UNION ALL
SELECT '5' AS rental_days FROM dual) dr INNER JOIN
common_lookup cl ON dr.rental_days = SUBSTR(cl.common_lookup_type,1,1)
WHERE NOT ((TRUNC (SYSDATE) - TRUNC (i.release_date)) <= 31 AND active_flag = 'N')
AND NOT cl.common_lookup_table = 'RENTAL_ITEM'
ORDER BY 1, 2, 3;
Any ideas on what I am doing wrong?
Upvotes: 2
Views: 421
Reputation: 85
Well I figured out what was wrong with my statement. I knew it was simple I just could not see it at the time. I forgot to take out the
ORDER BY 1, 2, 3;
it was messing everything up, sorry to take up anyone's time.
Upvotes: 2