-- When sequence.NEXTVAL is called in a query with DISTINCT, ORDER BY, GROUP BY
-- the call fails with ORA-02287 error.  By removing those conditions, 
-- the ORA-02287 can be resolved.

CREATE SEQUENCE seq_data_load_idx START WITH 1 INCREMENT BY 1 NOCACHE; 

WITH q_data AS (
SELECT 10000 + level part_id FROM DUAL
CONNECT BY level < 6)
SELECT
 part_id,
 seq_data_load_idx.NEXTVAL load_id,
 sysdate  load_date
FROM q_data
ORDER BY part_id        -- ORDER BY causes ORA-02287 error
;



WITH q_data AS (
SELECT 10000 + level part_id FROM DUAL
CONNECT BY level < 6)
SELECT
 part_id,
 seq_data_load_idx.NEXTVAL load_id,
 sysdate  load_date
FROM q_data
-- ORDER BY part_id
;



-- The ORA-02287 error also occurs in a query with UNION as shown below

SELECT
 part_id,
 seq_data_load_idx.NEXTVAL load_id,
 sysdate  load_date
FROM
 (SELECT 10000 + level part_id FROM DUAL CONNECT BY level < 6)
UNION
SELECT
 20001,
 0 load_id,
 sysdate  load_date
FROM DUAL;



-- The query below with UNION works without error (ORA-02287), designating
-- 0 as load_id for all part_id greater than 20000

WITH q_data AS
 (SELECT 10000 + level part_id FROM DUAL CONNECT BY level < 6
  UNION
 SELECT  20001 part_id FROM DUAL
),
q_load AS (
SELECT
 part_id,  sysdate  load_date
FROM q_data)
SELECT 
 part_id,
 DECODE(LEAST(part_id,20000), part_id,
              seq_data_load_idx.NEXTVAL, 0) load_id,
 load_date
FROM q_load
;