Empty rows...

Database
Enthusiast

Empty rows...

So this may seem like a silly question, but I am attempting to show data for certain periods of time for each fiscal year. However, some of the fiscal years do not have data for some of those periods of time, but the users would like to see zeros for them.

I suppose a summary of what I'm asking could be, how could I go about putting rows in my answerset that don't have any data to go with them?

Please feel free to ask any questions that would better help you understand what I'm looking for.
3 REPLIES
Senior Apprentice

Re: Empty rows...

In TD13.10 there's a new EXPAND ON query clause which is exactly what you need.
Whithout this feature is a typical solution based on a LEFT OUTER JOIN to a calendar table.

Dieter
Enthusiast

Re: Empty rows...

EXPAND ON operates on columns of PERIOD type. An outer join using a calendar table may still be the best solution with the traditional date type.

Here is a simple example:

create table sales (theDate date, theAmount decimal(10,2));
insert into sales (current_date, 9.99);

select
c.theYear as "Year",
coalesce(total_sales,0) as total_sales
from
(select distinct year_of_calendar
from sys_calendar.calendar
where year_of_calendar between 2008 and 2011) as c (theYear)
left join
(select sum(theAmount), extract(year from theDate) as theYear
from sales
group by 2) as sales_sum (total_sales, theYear)
on sales_sum.theYear = c.theYear
order by 1 desc
;

*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

Year total_sales
----------- -----------------
2011 9.99
2010 .00
2009 .00
2008 .00
Enthusiast

Re: Empty rows...

Thank you both!

I found another thread with the same suggestion before I saw this, and with a little tweaking I managed to get it. I was coming back to delete this thread when I saw these two suggestions.

Thanks again for your quick responses!