I have four fields -- itemnum, storeloc, binnum, and quantity.
I am trying to select data, where the first three fields match, but binnum is sometimes null. When I perform my sum on quantity, the calculation is not correct because of the NULL binnum. The database has an index where there can only be one itemnum, storeloc, and binnum combination where binnum can be NULL. I realize is it bad practice to allow a key to have NULL data, but I do not have control over how the data is structured.
I have tried the following where clauses:
where nvl(b.binnum,0) = nvl(mu.binnum,0)
where b.binnum is null and mu.binnum is null
Neither works. Any suggestions?
Using NVL to join fields that may have NULL values is the typical way to do this. Using this kind of query:
WHERE b.itemnum = mu.itemnum
AND b.storeloc = mu.storeloc
AND NVL(b.binnum, 0) = NVL(mu.binnum, 0)
assumes that there are never actually rows in either table having a binnum value of 0. If this is not the case, the sums will be off. You will join with the the binnum having a 0 value and the binnum having the NULL value. need to pick a default value for comparison that you know will never exist depending on your domain definitions. ie if binnum is NULL or greater than 0:
WHERE b.itemnum = mu.itemnum
AND b.storeloc = mu.storeloc
AND NVL(b.binnum, -1) = NVL(mu.binnum, -1)