title: Making an Oracle UNION query using the same data twice more efficient
body: <p>I'm wondering how I can do this without having to write out (or make the DB process) the main part of this query twice.</p>
  
  <p>My scenario is that I am querying an Oracle enterprise database (I am limited by the DBA, so some solutions MAY not work, but I'm not sure to what degree I'm limited until I try and don't know where to go with this yet).</p>
  
  <p>Currently, my query looks as follows:</p>
  
  <pre><code>    SELECT
          a.Field1,
          a.Field2,
          b.Field3,
          b.Field4,
          c.Field5
      FROM
          a,
          b,
          c,
      WHERE
              a.FieldX = b.FieldX
          AND
              b.FieldY = c.FieldY
  
  UNION
    (
      SELECT
          d.Field6 as Field1,
          d.Field7 + d.Field8 as Field2,
          MainQuery.Field3,
          MainQuery.Field4,
          MainQuery.Field5
      FROM
          (
              SELECT
                  a.Field1,
                  a.Field2,
                  b.Field3,
                  b.Field4,
                  c.Field5
              FROM
                  a,
                  b,
                  c,
              WHERE
                      a.FieldX = b.FieldX
                  AND
                      b.FieldY = c.FieldY
                  AND
                      a.FieldZ = 'XXXX'
          ) MainQuery,
  
          d
      WHERE
          MainQuery.Field1 = d.Field6
    )
  </code></pre>
  
  <p>Simply put, I have a main query (the first part) that returns the bulk of my results and then I'm repeating the main query with an extra limitation (<code>a.FieldZ = 'XXXX'</code>) and pulling data from this query AND a second table and appending it onto the main query using a <code>UNION</code>.</p>
  
  <p>The main challenge I'm facing is that the definitions of <code>Field1</code> &amp; <code>Field2</code> change between the 2 queries, so I can't figure out how to just do a simple join. But I'm sure there has to be a way to accomplish this without having to make the DB query the same data twice.</p>
  
  <p>Any thoughts about how to make this query more efficient??</p>
  
  <p>Thanks!!</p>
  
  <hr>
  
  <p>Based upon the comments, to explain a bit more about this query - What I am trying to do is get my entire unvierse of data in the first half of the query and then append on extra rows in the second half of the query for the records where <code>a.FieldZ = 'XXXX'</code> where the calculation is slightly different for <code>Field1</code> and <code>Field2</code></p>