title: PL/SQL: ORA-02287: sequence number not allowed here; DISTINCT subquery issues with REGEXP and CONNECT BY
body: <p>I am attempting to populate a bridge table with existing numeric <code>IDs</code> (<code>VARIABLE_ID</code> field) and strings (<code>DETAILS</code> field) that are in the format <code>averaged; smoothed</code> per cell. I've split the strings on <code>;</code> using <code>REGEXP_SUBSTR</code> and <code>CONNECT BY</code>, which works. When I try to insert the values into the new table,</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I get the error <code>PL/SQL: ORA-02287: sequence number not allowed here</code>, which makes sense. When I nest <code>DISTINCT</code> into the <code>FROM</code> clause (<code>SELECT DISTINCT VARIABLE_ID...FROM...</code>) it runs but I get a fundamentally different output.</p>
  
  <p>Appealing to only the <code>SELECT</code> clause, what I want is</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>which yields multiple <code>IDs</code> only if they are associated with multiple <code>DETAILS</code> in one cell (e.g., <code>averaged; smoothed</code>), <strong>which is what I want and it runs</strong>:</p>
  
  <p><a href="https://i.stack.imgur.com/nHVUh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nHVUh.png" alt="enter image description here"></a></p>
  
  <p>But, again, when I nest <code>DISTINCT</code> into the <code>FROM</code> clause, I get multiple <code>IDs</code> and multiple <code>DETAILS</code> (in fact, the total number of rows matches that of the table they are in):</p>
  
  <p><a href="https://i.stack.imgur.com/Wfev0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Wfev0.png" alt="enter image description here"></a></p>
  
  <p>Is there something under the hood I'm not seeing? What am I missing here?</p>