CODEWITHSUNDEEP
sqloracle-databaseoracle10g
srtmonkey
srtmonkey

Reputation: 85

ora-02287 Sequence number not allowed

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

Answers (1)

srtmonkey
srtmonkey

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

Related Questions