ORACLE SQL: Need to sum two values, but one may not exist... Is this possible?

Question

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.

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.

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.

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'

This is my first QUERY. If I add the conversion in:

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'

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.

Answer 1

Do not have access to Oracle right now. Check out this query 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 rth.item_code = cf.item_code (+) and cf.code = '01' and cf.loc_code = '00001'

Answer 2

Nope, I believe the OUTER JOIN doesn't work because the record is not actually there. It isn't null, it just doesn't exist anymore.

Answer 3

I think that should work. It assumes that conversion factor equals to 1 when it does not exist

select 
    rth.code, rth.number, rth.quantity, rth.sale_code, rth.quantity * coalesce(cf.conversion,1)
from 
    salesdetails rth
    inner join  salesheader rsh on rsh.number = rth.number
    left join conversionfactor cf on cf.item_code = rth.item_code
where 
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.code = '01'
and cf.loc_code = '00001'

Answer 4

I believe that SQL assumes I have a null record. I don't, I have no record at all when the conversion does not exist. If I can find a way to return a null for those CONVERSIONs that do not exist in my DB, I could do that.

Answer 5

It doesn't make sense to outer join from rsh to rth but then inner join from rth to cf. Make them both outer joins. Also, the (+) symbol must appear on the "outer" side of all join conditions for rth:

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'

This is very old-fashioned syntax, by the way. Why not use the more modern:

SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code,
       rth.quantity * cf.conversion
FROM salesheader rsh
LEFT OUTER JOIN salesdetails rth
   ON  rsh.number = rth.number
   AND rth.sale_code IN ('ITEM07')
   AND rth.c_code = 'WLMT'
LEFT OUTER JOIN conversionfactor cf
   ON  cf.item_code = rth.item_code
   AND cf.code = '01'
   AND cf.loc_code = '00001'
WHERE rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rsh.year = '2008'

Answer 6

You could also use the Coalesce function, with a subquery...

< Coalesce(arg0, arg1, arg2, ..... etc argN)

evaluates each argument in turn and returns the first one it finds that is not Null. >

Select D.CODE, D.NUMBER, D.QUANTITY, D.sale_code, 
    D.quantity * Coalesce((Select conversion 
                           From conversionfactor
                           Where item_code = H.item_code
                              And code = '01'
                              And loc_code = '00001'), 1.0)
From salesheader H
   Left Join salesdetails D
     On D.Number = H.Number
Where H.customer_code = '05'
   And H.r_code = '01'
   And H.location_code = '12'
   And H.year = '2008'
   And D.sale_code In ('ITEM07')
   And D.c_code = 'WLMT'

replace the 1.0 at the end of the subquery with whatever you want the "default" value of the conversion factor to be when the record is no longer in the conversionfactor table...

Answer 7

Actually, you're right about the OUTER JOIN. I was thinking there was a technicality in what it was doing, but I see exactly why it still wasn't including the value. The other two criteria needed to be (+) in order to include the NULL. I combined that with the COALESCE, and it seems to be working.

Answer 8

I imagine you are losing records because you are attempting to multiply by a null value. Take a look at using NVL() with your conversion factor to default it to 1 or some other default value that works for you.

Like,

SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code, rth.quantity * NVL(cf.conversion,1)
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'

Double check the NVL syntax though. It's been a couple years since I worked with Oracle.