how to filter out null value then compare in oracle

Question

I tried this, and it works,

SELECT name    
FROM    
  (SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL    
  )    
WHERE LENGTH >= ALL    
  (SELECT LENGTH FROM    
    (SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL    
    )    
  )

but my final code would be like this:

SELECT a.name,    
  a.length    
FROM    
  (SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL    
  ) a,    
  geo_river b,    
  encompasses c    
WHERE a.length >= ALL    
  (SELECT a2.LENGTH    
  FROM    
    (SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL    
    ) a2    
  )    
AND a.name      = b.river    
AND b.country   = c.country   
AND c.continent = 'America'

this is really complicated. Is there an easy way to let

(SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL)

be river, so I don't need to use this

(SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL)

two times.

score 0 · Accepted Answer · edited May 23 '17 at 10:28

If you want to simplify your code writing, you can use WITH:

with viewA as (SELECT name,LENGTH FROM river WHERE LENGTH IS NOT NULL )
SELECT a.name,    
  a.length    
FROM    
  viewA a,    
  geo_river b,    
  encompasses c    
WHERE a.length >= ALL    
  (SELECT a2.LENGTH    
  FROM    
    viewA a2    
  )    
AND a.name      = b.river    
AND b.country   = c.country   
AND c.continent = 'America'

MT0 · Answer 2 · 2016-02-25T23:37:42.260

Using a single table scan:

SELECT name,
       length
FROM   (
  SELECT name,
         length,
         RANK() OVER ( ORDER BY length DESC ) AS rnk
  FROM   river
)
WHERE rnk = 1;

So your code would then be:

SELECT a.name,    
       a.length    
FROM   (
         SELECT name,
                length
         FROM   (
           SELECT name,
                  length,
                  RANK() OVER ( ORDER BY length DESC ) AS rnk
           FROM   river
         )
         WHERE rnk = 1
       ) a
       INNER JOIN
       geo_river b
       ON ( a.name    = b.river )
       INNER JOIN
       encompasses c
       ON ( b.country = c.country )   
WHERE  c.continent = 'America';

score 0 · Answer 3 · answered Feb 26 '16 at 00:58

I don't think you need to filter out null lengths as they won't show up if you subset on length (i.e. when comparing NULL values without using the NVL function the comparison will always evaluate to false and not show the row). So something simple such as:

Select a.name, a.length 
from river a, geo_river b,  encompasses c   
WHERE a.length > 0 
AND a.name      = b.river  
AND b.country   = c.country 
AND c.continent = 'America' 
;

Will do the trick

how to filter out null value then compare in oracle

3 Answers3