1

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?

Sun
  • 2,595
  • 1
  • 26
  • 43
  • When the first three fields match...what? You seem to have two tables. It would help to see your table structure and whole query. – rfusca Feb 12 '13 at 01:25
  • I do have two tables. When the first fields match and binnum is NOT NULL the sum calculation works fine. The query is a bit long-winded so I'd prefer general advice, but I can post it if you really want to see it. – Sun Feb 12 '13 at 01:31

1 Answers1

4

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)
Glenn
  • 8,932
  • 2
  • 41
  • 54
  • thanks for your answer. I will try your suggestion at work tomorrow. :) Why was I under the impression that NVL could only use a number as a replacement? http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions055.htm -- I can use a string as well? Any pros or cons to using a number vs string? Can the string be concatenated fields? – Sun Feb 12 '13 at 02:53
  • What happens if one table returns a value (a numeric), but your NVL clause returns a String? Then you will be comparing two different types. Safer to ensure consistency in comparing types. – Glenn Feb 12 '13 at 02:58
  • Sorry, I should have stated previously that BINNUM is a VARCHAR2 field. The NUM in binnum is misleading. – Sun Feb 12 '13 at 03:04
  • Then compare varchars: `NVL(b.binnum, '!NULL_BIN_COMPARE!') = NVL(mu.binnum, '!NULL_BIN_COMPARE!')` – Glenn Feb 12 '13 at 03:07
  • Thank you. When I performed a count of binnum where binnum like '0%', I found 38 rows. That could have been causing the issue... but I think I also received a poorly written query to work with. I rewrote using sub-selects instead of group by and now your NVL(b.binnum, '!NULL_BIN_COMPARE!') = NVL(mu.binnum, '!NULL_BIN_COMPARE!') is working as expected. – Sun Feb 12 '13 at 19:59