title: Selecting a sum from a correlated subquery - Oracle
body: <p>I'm trying to change my code to have Oracle do all the heavy lifting. Originally, I had a function which did the following:</p>
  
  <pre><code>&lt;!--- get common data ---&gt;
  &lt;cfquery name="getCommon"&gt;
      SELECT  x, NVL(SUM(y), 0) as y
      FROM    table_1
      WHERE   z between #this# and #that#
      GROUP BY x
  &lt;/cfquery&gt;
  
  &lt;!--- place common values into a struct ---&gt;
  &lt;cfset oCommon = StructNew() &gt;
  &lt;cfloop query="getCommon"&gt;
      &lt;cfset oCommon[x] = y &gt;
  &lt;/cfloop&gt;
  
  &lt;!--- get records to loop through ---&gt;
  &lt;cfquery name="getSomething"&gt;
      SELECT a, b, c/d as e from (
          SELECT DISTINCT t2.a, 
                  t2.b, 
                  c,
                  sum(d)as d
          FROM table_2 t2
          LEFT JOIN table_3 t3
          ON t2.a = t3.a
          AND t2.b = t3.b
          GROUP BY t2.a, t2.b, c
      )
  &lt;/cfquery&gt;
  
  &lt;!--- loop through the results ---&gt;
  &lt;cfloop query="getSomething"&gt;
      &lt;!--- do an update to the target table if conditions are met ---&gt;       
  
      &lt;!--- calculate my important new value ---&gt;
      &lt;cfset new_value = #getSomething.e# * #oCommon[b]# &gt;
  
      &lt;!--- finally, insert my new values into the target table ---&gt;
      &lt;cfquery name="insertTarget"&gt;
          INSERT INTO target_table(
                  a, 
                  b, 
                  c
          )
          VALUES ( 
              #getSomething.a#,
              #getSomething.b#,
              #new_value# 
          )
      &lt;/cfquery&gt;
  &lt;/cfloop&gt;
  </code></pre>
  
  <p>I've already figured out how to use Oracle's MERGE to replace the update statement in the loop. My problem is the SQL to replace the insert statement. I'd like to be able to calculate the new value inside the SQL... once I get that, I think I can figure out the MERGE/insert part fairly easily.</p>
  
  <p>So I want to do something like:</p>
  
  <pre><code>    SELECT a, b, c/d*y as e from (
          SELECT DISTINCT t2.a, 
              t2.b, 
              c,
              sum(d)as d,
              (
                  SELECT y FROM (
                      SELECT  x, NVL(SUM(y), 0) as y
                      FROM    table_1
                      WHERE   z between #this# and #that#
                              AND x = t2.a
                      GROUP BY x
                  )
              )       
          FROM table_2 t2
          LEFT JOIN table_3 t3
          ON t2.a = t3.a
          AND t2.b = t3.b
          GROUP BY t2.a, t2.b, c
      )
  </code></pre>
  
  <p>which, perhaps obviously, doesn't work. Currently I get an ORA-00904: "t2"."a": Invalid Identifier, which isn't surprising.</p>
  
  <p>So, I basically need to figure out how to get the sum of y from table_1, correlated by x = table_2.a, into the original getSomething query. </p>
  
  <p>Edit: I tried this, but it's not quite right:</p>
  
  <pre><code>SELECT a, b, c/d*y as e from (
          SELECT DISTINCT t2.a, 
              t2.b, 
              c,
              sum(d)as d,
               NVL(SUM(y), 0) as y     
          FROM table_2 t2
          LEFT JOIN table_3 t3
          ON t2.a = t3.a
          AND t2.b = t3.b
          LEFT JOIN table_1 t1
          ON t1.x = t2.a
          AND t1.z between #this# and #that#
          GROUP BY t2.a, t2.b, c
      )
  </code></pre>