Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: Return zero if query returns nothing?

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    pseudo code:
    Code:
    SELECT Coalesce(b.the_sum, a.number)
    FROM   (
            SELECT 0 As number
          ) As a
     CROSS
      JOIN (
            < insert your query >
          ) As b
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Posts
    12
    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.
    Last edited by NickJ; 06-17-09 at 18:21.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like you might want to use a LEFT OUTER JOIN, to return kpifntimes that don't have a kpiddate (whatever those are)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2003
    Posts
    12
    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).
    Last edited by NickJ; 06-17-09 at 18:44.

  7. #7
    Join Date
    Sep 2003
    Posts
    12
    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...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The cross join method should work just fine - did you try it and see?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •