Q:

PL/SQL: ORA-02287: sequence number not allowed here; DISTINCT subquery issues with REGEXP and CONNECT BY

I am attempting to populate a bridge table with existing numeric IDs (VARIABLE_ID field) and strings (DETAILS field) that are in the format averaged; smoothed per cell. I've split the strings on ; using REGEXP_SUBSTR and CONNECT BY, which works. When I try to insert the values into the new table,
INSERT INTO PC_VARIABLES_DETAILS_BRIDGE (DETAIL_BRIDGE_ID, VARIABLE_ID, THE_DETAIL)
SELECT DISTINCT PC_VAR_DETAILS_BRIDGE_PK.NEXTVAL, e.VARIABLE_ID, TRIM(REGEXP_SUBSTR(e.DETAILS,'[^;]+', 1, LEVEL)) FROM (SELECT VARIABLE_ID, DETAILS FROM EG_VAR_UPLOAD_TEST WHERE DETAILS IS NOT NULL) e
CONNECT BY TRIM(REGEXP_SUBSTR(e.DETAILS, '[^;]+', 1, LEVEL)) IS NOT NULL;

I get the error PL/SQL: ORA-02287: sequence number not allowed here, which makes sense. When I nest DISTINCT into the FROM clause (SELECT DISTINCT VARIABLE_ID...FROM...) it runs but I get a fundamentally different output.
Appealing to only the SELECT clause, what I want is
SELECT DISTINCT e.VARIABLE_ID E_VARIABLE_ID, TRIM(REGEXP_SUBSTR(e.DETAILS,'[^;]+', 1, LEVEL)) THE_DETAIL FROM (SELECT VARIABLE_ID, DETAILS FROM EG_VAR_UPLOAD_TEST WHERE DETAILS IS NOT NULL) e
        CONNECT BY TRIM(REGEXP_SUBSTR(e.DETAILS, '[^;]+', 1, LEVEL)) IS NOT NULL ORDER BY e.VARIABLE_ID;

which yields multiple IDs only if they are associated with multiple DETAILS in one cell (e.g., averaged; smoothed), which is what I want and it runs:

But, again, when I nest DISTINCT into the FROM clause, I get multiple IDs and multiple DETAILS (in fact, the total number of rows matches that of the table they are in):

Is there something under the hood I'm not seeing? What am I missing here?

A:

It looks like you just need one more level in your select. Once you've gathered all of the distinct values, select all of them and add in the sequence call in an outer query. For example:
INSERT INTO pc_variables_details_bridge
   (detail_bridge_id
   ,variable_id
   ,the_detail)
   SELECT pc_var_details_bridge_pk.nextval
         ,variable_id
         ,the_detail
     FROM (SELECT DISTINCT e.variable_id
                          ,TRIM(REGEXP_SUBSTR(e.details, '[^;]+', 1, LEVEL)) the_detail
             FROM (SELECT variable_id
                         ,details
                     FROM eg_var_upload_test
                    WHERE details IS NOT NULL) e
           CONNECT BY TRIM(REGEXP_SUBSTR(e.details, '[^;]+', 1, LEVEL)) IS NOT NULL);

