title: SQL - extract random sample from joined tables
body: <p>I've to fill an empty table (TAB_A) with random records (5%) from TAB_B, joined with other 3 tables (C1, C2, C3).</p>
  
  <p>Now, if I run the following query everything works well:</p>
  
  <pre><code>INSERT INTO TAB_A (field1,field2)
      SELECT TAB_B.ID_TASS, TAB_B.ID_SEZ
      FROM (SELECT TAB_B.*, row_number() 
              OVER (ORDER BY dbms_random.VALUE) r 
              FROM TAB_B) 
      WHERE r &lt; (SELECT COUNT(*) FROM TAB_B)*0.05
  </code></pre>
  
  <p>The problem is that I need also to fill in the TAB_A.field3 whose values are in TAB_C3.
  So I rewrite my previous query:</p>
  
  <pre><code>INSERT INTO TAB_A (field1,field2,field3)
      SELECT B.ID_TASS, B.ID_SEZ, C3.Z_ID
      FROM (SELECT B.*, row_number() 
              OVER (ORDER BY dbms_random.VALUE) r 
              FROM TAB_B B
                  JOIN TAB_C1 C1 on C1.X_ID = B.X_ID
                  JOIN TAB_C2 C2 on C2.Y_ID = C1.Y_ID
                  JOIN TAB_C3 C3 on C3.Z_ID = C2.Z_ID
              WHERE C3.Z_ID = 9) 
      WHERE r &lt; (SELECT COUNT(*) FROM B)*0.05
  </code></pre>
  
  <p>but an error occurs: ORA-00904: "C3.Z_ID" invalid identifier.</p>
  
  <p>I think the problem could be in the statement "<strong>SELECT B.ID_TASS, B.ID_SEZ, C3.Z_ID</strong>", that I changed in "<strong>SELECT TAB_B.ID_TASS, TAB_B.ID_SEZ, TAB_C3.Z_ID</strong>".... but without success: the same error occurs again.</p>
  
  <p>By the way, the following query works perfectely:</p>
  
  <pre><code>select count(*)
      from TAB_B B
          JOIN TAB_C1 C1 on C1.X_ID = B.X_ID
          JOIN TAB_C2 C2 on C2.Y_ID = C1.Y_ID
          JOIN TAB_C3 C3 on C3.Z_ID = C2.Z_ID
      WHERE C3.Z_ID = 9
  </code></pre>
  
  <p>Can anybody help me?</p>