Automatic Fetch one record

Question

I am execute below query. there are no data but SQL Nevigator fetch ONE record and this record are show the blank. so i want no fetch any record.

select sum(arrears_edutax) 
  from view_govtax_rpt 
    where trunc(receiptdt) between  '01-Oct-2019' and '14-Oct-2019';

But dont use Group BY function.

Looks like column `ARREARS_EDUTAX` can have null values. Have you seen [Sum columns with null values in oracle](https://stackoverflow.com/questions/590551/sum-columns-with-null-values-in-oracle)? — Abra, Oct 15 '19 at 12:33
If you need all data you cannot use sum in this way. you need to use window function — Derviş Kayımbaşıoğlu, Oct 15 '19 at 12:38
I am Already use is not,NVL,ISNULL and trim but they are not work. @Abra — Kiran Patil, Oct 15 '19 at 12:41
But i want diff-diff date a data.. so i have need to group by. — Kiran Patil, Oct 15 '19 at 12:48
It's really not clear what problem you're trying to solve. I made a guess but I think my answer has solved your problem. So I suggest you post some sample data and your desired output derived from that sample. — APC, Oct 15 '19 at 13:36
i am type wrong Sorry For mistake. I want diff-diff date from database . so i dont need group by. — Kiran Patil, Oct 16 '19 at 05:29

score 0 · Answer 1 · answered Oct 15 '19 at 12:28

0

Quick demonstration about what you need

with w0 as 
(
  select 1 x from dual union
  select 2 from dual
), w1 as
(
   select * from w0 where 1=2
)
select c1 from (
  select sum(x) c1 from w1
)
where c1 is not null

Click here for demo

answered Oct 15 '19 at 12:28

Derviş Kayımbaşıoğlu

28,492
4
50
72

Its Not Work.. that through only fetch 0 record. – Kiran Patil Oct 15 '19 at 12:39

score 0 · Answer 2 · answered Oct 15 '19 at 13:18

0

Try this query

 select sum(null) c1 from dual where 1=2

No data due to 1=2 but one row result

answered Oct 15 '19 at 13:18

Marmite Bomber

19,886
4
26
53

Ok. I understand. Thank you – Kiran Patil Oct 15 '19 at 13:28

APC · Accepted Answer · 2019-10-15T13:26:14.727

there are no data but SQL Nevigator fetch ONE record and this record are show the blank. so i want no fetch any record

That's not how aggregate queries work.

An aggregate query with no GROUP BY returns one row. The result is the aggregation applied to the filtered rows. Because you had no rows which matched your criteria the aggregated value is NULL. If you had used a COUNT() function instead you would have got zero.

So, if you really want an empty result set (zero rows) when there's no matching data you can use this trick:

select sum(arrears_edutax) 
from view_govtax_rpt 
where trunc(receiptdt) between  date '2019-10-01' and date '2019-10-14'
having count(*) > 0
;

Incidentally, you should get into the habit of using date literals instead of relying on implicit data conversion.

Automatic Fetch one record

3 Answers3