title: PL/SQL Using aliases and functions in the subquery of an insert statement
body: <p>I'm pretty new to PL/SQL, but I was wondering if there was a way to achieve the following :</p>
  
  <pre><code>   INSERT  INTO aTable (
     SELECT  FN_TO_VALUE(b.field1, b.field2, b.field3) as alias1,
             FN_TO_VALUE(b.field1, b.field2, b.field3) as alias2,
             FN_TO_VALUE(b.field1, b.field2, b.field3) as alias3,
             c.field1, c.field2
     FROM    bTable b, cTable c
     WHERE   b.alias1= c.field3
     );
  </code></pre>
  
  <p>Given that <strong>FN_TO_VALUE</strong> is a function that returns the corresponding datatype that would be in the <strong>aTable</strong>.</p>
  
  <p>I get the following error statements : </p>
  
  <pre><code>PL/SQL : SQL Statement ignored (that's located at the INSERT INTO line)
  PL/SQL : ORA-00904: "B"."ALIAS1" invalid identifier
  </code></pre>
  
  <p><strong>ERRATA :</strong></p>
  
  <ul>
  <li>I had to modify the where clause from <strong>WHERE   b.alias1= c.field1</strong> to <strong>WHERE   b.alias1= c.field3</strong> because one of the solution provided by @Bob Jarvis wouldn't take into consideration other fields that might be required to link table bTable and cTable.</li>
  </ul>
  
  <p><strong>SOLUTION :</strong></p>
  
  <ul>
  <li><p>I have opted for the following solution : </p>
  
  <pre><code> INSERT  INTO aTable (
     SELECT b.alias1,
            b.alias2,
            b.alias3,
            c.field1,
            c.field2
     FROM cTable c, 
     (
       SELECT FN_TO_VALUE (field1, field2, field3) AS alias1,
              FN_TO_VALUE (field1, field2, field3) AS alias2,
              FN_TO_VALUE (field1, field2, field3) AS alias3
       FROM bTable 
     ) b
     WHERE b.alias1 = c.field3
  );
  </code></pre></li>
  </ul>