This is a pretty common problem that I run into and I have solved it two different ways in the past.
Basically you want a trending report over a date range, but data isn't always available. You want a zero for those dates, not a missing record. The first way is to determine your start and end date, then loop manually over those dates processing what data might exist as you go. This is pretty RBAR and not my favorite. The second way is to create a temp table (or table variable in MS SQL) and populate it with all the dates you wish to capture. Then select from that table in your report first and LEFT OUTER join to all of your other tables joining to your temp table on date and making sure your select statement uses the date column from the temp table. This will ensure your result set has a record for every date you want. (It doesn't even have to be dates, you can do this with products, etc). Also make sure you use isnull to turn NULLs into 0. Now it sounds like you have been trying the second approach unsuccessfully. This is because you actually want to ensure the Cartesian product of TWO things are present in your result set. Basically, you want a record for each COUNTRY for each MONTH. To solve this I would create a month table AND a country table. Start your select by joining both of them together on 1 = 1 (I assume oracle will let you do that) That result set will give you every possible combination of month and country in your range. Now, LEFT OUTER everything else to that joining on country AND month. Ensure that your select, order by, and grouping all reference the country and month in the temp tables since they can be null elsewhere. This should work pretty well for you. I literally just did this yesterday across three axes. I had month, product, and source and I needed all possible data points represented so I could calculate projection data for a line graph. I did it with three temp tables and it worked great. Also, putting your Cartesian product in a temp table right off the bat, and then joining in your actual data might help clean up the code. Oh- and I forgot-- isnull is probably a MS SQL thing-- you will probably need coalesce, if Oracle is like MySQL and DB2. ~Brad ----- Original Message ----- From: "Ian Skinner" <[EMAIL PROTECTED]> To: "cf-talk" <[email protected]> Sent: Wednesday, November 05, 2008 9:13 AM Subject: Keep empty records. >I have a multi-level report that I am having trouble building a proper > record set for given the structure of the data I have to work with. I'm > hoping the illustrious minds on this list can help me see the light. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314850 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
