CODEWITHSUNDEEP
sqloracle-database
user1819920
user1819920

Reputation: 2248

How to show 0 when no row found

I have a SQL query in which I am passing sysdate to the query problem is that when there is no matching date in table with sysdate then it don't shows the zero even if there is nvl applied here is my query 

select * from molasses
where trunc(trn_dte) = trunc(sysdate)

But it show data only when current date is present in table but I want to show zero if no data found in table.please help me to do this in oracle 10 g. Because some times the situation is like above and I have to display zero when no data found

Upvotes: 0

Views: 4966

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

This is weird and I wouldn't use it, it's more of a hack:

SELECT col1, col2, ..., colN              --- numeric columns
FROM molasses
WHERE trunc(trn_dte) = trunc(sysdate)

UNION ALL

SELECT 0, 0, ..., 0
FROM dual
WHERE NOT EXISTS
      ( SELECT *
        FROM molasses
         WHERE trunc(trn_dte) = trunc(sysdate)
      ) ;

One only wonders what the application/user will understand when there is exactly one row in the table and all the values are zero.

I think this would work, too:

SELECT m.col1, m.col2, ..., m.colN              --- numeric columns
FROM dual LEFT JOIN molasses m
  ON trunc(m.trn_dte) = trunc(sysdate) ;

and show Nulls instead of (the wanted) 0s. Using the COALESCE() function could easily fix that, as well.

Upvotes: 1

Rene
Rene

Reputation: 10551

This is a very unusual question. Why should a query return 0's when no data present? Usually it's up to the client software/report generator to show something meaningful when no data is present, not the query itself.

Upvotes: 1

Codo
Codo

Reputation: 79023

Assuming your table has four numeric columns, you could write:

select * from molasses
where trunc(trn_dte) = trunc(sysdate)
union all
select 0, 0, 0, 0 from dual
where ( select count(*) from molasses where trunc(trn_dte) = trunc(sysdate) ) = 0

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174457

Something like this would work, but I don't think it is a very good idea:

select nvl(t.a, n.a) as a, nvl(t.b, n.b) as b, ...
from molasses t
right outer join (select 0 as a, 0 as b, ... from dual) n on 1 = 1

Upvotes: 2

Related Questions