CODEWITHSUNDEEP
sqloracle11g
DatRid
DatRid

Reputation: 1169

SQL doesn't return correct values

So, I have a simple SQL query, which however seems to be bugged (or my where-clause is written wrong), as it doesn't return a value if I select on a specific field (matflag) with a specific value (50).

The query is basically a select from table1 with a subquery on table2 where the where-clause just checks if the returned field from the subquery exists in table1:

Select distinct  
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum and t1.matnum = '60000000';

This returns this output:

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+
| 60000000 |         | 001000  |
| 60000000 |         | 002000  |
| 60000000 |         | 003000  |
| 60000000 |         | 004000  |
| 60000000 |         | 005000  |
+----------+---------+---------+

If I, however add and t1.matflag != '50' to the end of the where-clause, the whole output disappears.

Select distinct 
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum 
    and t1.matnum = '60000000' 
    and t1.matflag != '50';

Output:

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+

Additional information for the column matflag: It is a varchar2(2 Char) column, either filled with nothing or the value '50' or the value '10' or '20'. Now, if I change the where clause from and t1.matflag != '50' to and t1.matflag is null, the output is correct again:

Select distinct 
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum 
    and t1.matnum = '60000000' 
    and t1.matflag is null;

So this returns this output:

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+
| 60000000 |         | 001000  |
+----------+---------+---------+
.... and so on, have a look at the first table above

So how does it return something if I select on is null but not if I select on != '50'? (Sidenote: changing != to <> didn't help either)

How does matflag is null apply but matflag != '50' not?

We run the Oracle Database 11g Release 11.2.0.3.0 - 64bit Production.

Upvotes: 2

Views: 124

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6309

In SQL NULL means "unknown" value, thus any operation with it will result in NULL. Try COALESCE(t1.matflag, 0) <> 50...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Learn how to use proper explicit JOIN syntax:

Select distinct t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from table1 t1 join
     table2
     on t1.matnum = t2.matnum
where t2.technical_value = 'XX' and t1.matnum = '60000000';

Then learn about NULL values and how they fail almost every comparison, including <>.

The logic you want is:

where t2.technical_value = 'XX' and t1.matnum = '60000000' and
      (matflag <> '50' or matflag is null)

Upvotes: 3

Related Questions