Return zero if query returns nothing?

NickJ · #1 (**permalink**) 06-17-09, 14:37

Hi,

I have the following query, which does not return any results.

Code:

select (sum(t.minutes) / 60),d.fiscalmonthnum from kpifntime t
	inner join kpiddate d on t.datekey=d.datekey
	inner join kpidacts a on t.activitykey=a.activitykey
	inner join kpidfees f on t.feeearnerkey=f.feeearnerkey	
where a.activitycode in (11,12,13,14,15,16,17,18)
and d.fiscalmonthnum=11
and d.fiscalyearnum=2009
and t.feeearnerkey=520
group by fiscalmonthnum;

If the sum of t.minutes does not return a result, I need the query to return the integer 0.

I've tried this using the IFNULL() function, but I don't think this is correct as the result being returned from the query is not null, just blank.

Any ideas guys?

gvee · #2 (**permalink**) 06-17-09, 14:49

pseudo code:

Code:

SELECT Coalesce(b.the_sum, a.number)
FROM   (
        SELECT 0 As number
      ) As a
 CROSS
  JOIN (
        < insert your query >
      ) As b

r937 · #3 (**permalink**) 06-17-09, 14:53

Quote:

Originally Posted by NickJ

... the result being returned from the query is not null, just blank.

it's ~not~ blank

where are you running the query?

NickJ · #4 (**permalink**) 06-17-09, 16:53

Thanks for the reply guys.

I thought about coalesce but not sure this will work as the sum(minutes) does not return a NULL, the records simply do not exist in the database.

Say for example,

Code:

select price from products
where productname = 'does not exist in database'

... How can I get this query to return the value 0, where the product does not exist??

Would the following work? I think it would just return no record as b.price does not equate to NULL.

Code:

SELECT Coalesce(b.price, a.number)
FROM   (
        SELECT 0 As number
      ) As a
 CROSS
  JOIN (
       select price as price from products 
       where productname = 'does not exist in database'
      ) As b

r937, i'm not sure what you meant by your post. I'm running the query in mysql GUI and quest's toad?

I need the query to return a 0 as i'm using this query as a subquery in an update statement, and using the result to deduct off the value i'm updating.

I'm using mysql 4.1.

r937 · #5 (**permalink**) 06-17-09, 17:23

sounds like you might want to use a LEFT OUTER JOIN, to return kpifntimes that don't have a kpiddate (whatever those are)

NickJ · #6 (**permalink**) 06-17-09, 17:33

There are no entries to return in the kpifntime table for a particular month and year, this is whats causing me the problem.

Code:

update kpifbudget b
set budgetamount=budgetamount - (
                                select (sum(t.minutes) / 60) from kpifntime t
                                  inner join kpiddate d on t.datekey=d.datekey
                                  inner join kpidacts a on t.activitykey=a.activitykey
                              		inner join kpidfees f on t.feeearnerkey=f.feeearnerkey
                                where a.activitycode in (11,12,13,14,15,16,17,18)
                                and b.fiscalmonth=d.fiscalmonthnum
                                and b.fiscalyear=d.fiscalyearnum
                                and b.feeearnerkey=t.feeearnerkey)
where b.budgetkey=1;

When the correlated subquery returns no record for the sum(t.minutes), i'm trying to deduct it from budgeamount. This results in budgetamount being set to NULL!

The kpifbudget table has entries for every fiscal month and year, whereas there are not always records in kpifntime table for every fiscal month and year (stored in the kpiddate table joined to kpifntime).

NickJ · #7 (**permalink**) 06-17-09, 18:06

think i've cracked it,

Code:

update kpifbudget b
  inner join kpifntime nont on b.feeearnerkey=nont.feeearnerkey
  inner join kpiddate dated on nont.datekey=dated.datekey
  inner join kpidacts act on nont.activitykey=act.activitykey
set budgetamount=budgetamount - (
                                select (sum(t.minutes) / 60) from kpifntime t
                                  inner join kpiddate d on t.datekey=d.datekey
                                  inner join kpidacts a on t.activitykey=a.activitykey
                              		inner join kpidfees f on t.feeearnerkey=f.feeearnerkey
                                where a.activitycode in (11,12,13,14,15,16,17,18)
                                and b.fiscalmonth=d.fiscalmonthnum
                                and b.fiscalyear=d.fiscalyearnum
                                and b.feeearnerkey=t.feeearnerkey)
where b.budgetkey=1
and b.feeearnerkey=520
and b.fiscalyear=2009
and act.activitycode in (11,12,13,14,15,16,17,18)
and dated.fiscalmonthnum=b.fiscalmonth
and dated.fiscalyearnum=b.fiscalyear;

.... my head hurts...

gvee · #8 (**permalink**) 06-18-09, 05:22

The cross join method should work just fine - did you try it and see?

Internet Services

Web Development

Digital Marketing

Consumer Tech