CODEWITHSUNDEEP
sqldatabaseoracle-database
Kristiana
Kristiana

Reputation: 93

How to handle with NULL values

I have the following code:

`SELECT nt.pl,
         o.cat,
         o.coun,
         COUNT (DISTINCT nt.im) Sub,
         SUM (nt.DOWN + nt.UP) Vol,
         COUNT (*) AS counter,
         CASE
            WHEN nt.tele = '5' THEN 'Vo'
            WHEN nt.tele = ('N/A') AND nt.rat = ('G') THEN 'G'
            ELSE 'Unknown'
         END
            AS Service
    FROM tmp nt, description o
   WHERE nt.pl = o.pl AND nt.time LIKE '201605%'
GROUP BY nt.pl,
         o.cat,
         o.coun,
         nt.tele,
         nt.rat`

The problem is that the column rat contains NULL values and in the result I receive more rows than expected. How I could handle with these NULL values.

Thanks

Upvotes: 0

Views: 77

Answers (2)

The scion
The scion

Reputation: 972

You are trying to filter the null value? 

 WHERE nt.pl = o.pl AND nt.time LIKE '201605%' AND nt.rat is not null

??

Upvotes: 0

Boneist
Boneist

Reputation: 23588

I suspect that rather than having the nt.tele and nt.rat columns in the group by list, you want the case expression instead, e.g.:

  SELECT nt.pl,
         o.cat,
         o.coun,
         COUNT (DISTINCT nt.im) Sub,
         SUM (nt.DOWN + nt.UP) Vol,
         COUNT (*) AS counter,
         CASE
            WHEN nt.tele = '5' THEN 'Vo'
            WHEN nt.tele = ('N/A') AND nt.rat = ('G') THEN 'G'
            ELSE 'Unknown'
         END
            AS Service
    FROM tmp nt, description o
   WHERE nt.pl = o.pl AND nt.time LIKE '201605%'
GROUP BY nt.pl,
         o.cat,
         o.coun,
         CASE
            WHEN nt.tele = '5' THEN 'Vo'
            WHEN nt.tele = ('N/A') AND nt.rat = ('G') THEN 'G'
            ELSE 'Unknown'
         END;

Upvotes: 3

Related Questions