Remove the Null Value from the result generated from MAX() used in query sql

Question

When i use the query:

select MAX(DT_LOGDATE)
from   UMS_LOGENTRY_DTL
where  C_INPUTMODE='R' and VC_DEVICEID=10

the table gives maximum date, but if the condition is not satisfied it returns a data table with NULL. so there will be a null row. But I need it as empty data table. How I remove that null on noresult?

Answer 1

You could use HAVING clause:

SELECT MAX(DT_LOGDATE)
FROM   UMS_LOGENTRY_DTL
WHERE  C_INPUTMODE='R' and VC_DEVICEID=10
HAVING MAX(DT_LOGDATE) IS NOT NULL

Answer 2

Use a derived table and filter on that:

select *
from (
  select MAX(DT_LOGDATE) as max_date
  from   UMS_LOGENTRY_DTL
  where  C_INPUTMODE='R' 
  and VC_DEVICEID=10
) t
where max_date is not null

Answer 3

Scalar aggregates always return a single row even if aggregating an empty set.

You can also do

SELECT MAX(DT_LOGDATE)
FROM   UMS_LOGENTRY_DTL
WHERE  C_INPUTMODE = 'R'
       AND VC_DEVICEID = 10 
GROUP BY () 

The GROUP BY clause makes it a vector aggregate rather than scalar and no NULL row will be returned if there are no rows in UMS_LOGENTRY_DTL that match the WHERE.