title: ORACLE SQL: Need to sum two values, but one may not exist... Is this possible?
body: <p>I've been running into some problems duplicating some old ASP files into some newer .NET 2.0 code. One of the tasks is to merge the 4-5 SQL statements into one. While I have done this and had some success in performance boosts, Oracle is a new bag for me. This problem however surpasses my own SQL skills as I haven't done this before.</p>
  
  <p>Basically, I have a QUANTITY in one table from a bunch of Sales. Each sale has an integer value. Each sale also has an ITEM attached to it. Each ITEM has a CONVERSION factor as in if I sell 1 bag of something = 10 bundles of something. So, when I run a report and want to find out the end value, I need to take each sale and its quantity and multiple it by its conversion factor. Most are simple 1 to 1, so it's basically doing 25 * 1, 30 * 1 etc.</p>
  
  <p>My problem is that there are past sales in my records in which the ITEM has been removed from our system, therefore the CONVERSION factor does not exist. Those records get dropped from my query because the FACTOR is gone.</p>
  
  <pre><code>SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code 
  FROM salesdetails rth, salesheader rsh
  WHERE rsh.number = rth.number(+)
  AND rsh.customer_code = '05'
  AND rsh.r_code = '01'
  AND rsh.location_code = '12'
  AND rth.sale_code IN('ITEM07')
  AND rth.c_code = 'WLMT'
  AND rsh.year = '2008'
  </code></pre>
  
  <p>This is my first QUERY. If I add the conversion in:</p>
  
  <pre><code>SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code, rth.quantity * cf.conversion
  FROM salesdetails rth, salesheader rsh, conversionfactor cf
  WHERE rsh.number = rth.number(+)
  AND rsh.customer_code = '05'
  AND rsh.r_code = '01'
  AND rsh.location_code = '12'
  AND rth.sale_code IN('ITEM07')
  AND rth.c_code = 'WLMT'
  AND rsh.year = '2008'
  AND cf.item_code = rth.item_code
  and cf.code = '01'
  and cf.loc_code = '00001'
  </code></pre>
  
  <p>This works to an extent. It lists all the same records, but it is missing any records in which the CONVERSION factor did not exist. Is there anyway I can still include those records where the FACTOR didn't exist in the second query, short from going line by line and doing the conversion that way.</p>