1 Replies - 2005 Views - Last Post: 29 August 2012 - 08:06 AM

#1 kaptenhisyam  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 21-October 09

SQL: cannot echo result when one row is empty

Posted 29 August 2012 - 01:45 AM

$pdf->Table('select @N:=@N+1 AS no,
                    A.department as "DEPARTMENT", 
                    A.no as "ASSET NUMBER" , 
                    A.total as "ASSET TOTAL PRICE" , 
                    B.no as "INVENTORY NUMBER" ,  
                    B.total as "INVENTORY TOTAL PRICE" 
                    (select  h.department , coalesce(count(h.id),0) as no,coalesce(sum(h.price),0) as total 
                        from asset h 
                        where h.department = "'.$department.'" 
                        and  year(h.date_accepted) = '.$year.' 
                        GROUP BY year(h.date_accepted)) as  A , 
                    (select coalesce(count(i.id),0) as no,coalesce(sum(i.price),0) as total 
                        from inventory i 
                        where i.department = "'.$department.'"
                        and  year(i.date_accepted) = '.$year.' 
                        GROUP BY year(i.date_accepted)) as B   ',$prop);

I'm doing a PDF file that will generate table that read from SQL. The problem is when any row in database is empty the PDF Table will not generate any output even though there are data in other table .

e.g. for year 2009 there are only Assets accepted while there's none for Inventory. When echoing, the table generated is empty even though there's data in Asset table in the database.

I've tried everything from ISNULL, IFNULL, COALESCE to set the value of sum()/count() to 0(which I think the reason why the table generated is empty) but still can't generate the data.

Why did the COALESCE function not functioning and is it because of the use of alias?eg COALESCE(sum(i.price),0)) (as in i.variable)

Is This A Good Question/Topic? 0
  • +

Replies To: SQL: cannot echo result when one row is empty

#2 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1001
  • View blog
  • Posts: 3,555
  • Joined: 05-June 09

Re: SQL: cannot echo result when one row is empty

Posted 29 August 2012 - 08:06 AM

Have you tried running your subqueries individually and finding out what the actual output of your COALESCE is?

Running them individually will help you to debug further, rather than trying to debug them whilst they're embedded inside a parent query.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1