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.
Here is the desired output. Each year should always list all counties.
YEAR 1
COUNTY A
Jan Records | Feb Records | Mar Records ... Dec Records
COUNTY B
Jan Records | Feb Records | Mar Records ... Dec Records
...
COUNTY Z
Jan Records | Feb Records | Mar Records ... Dec Records
YEAR 2
COUNTY A
Jan Records | Feb Records | Mar Records ... Dec Records
COUNTY B
Jan Records | Feb Records | Mar Records ... Dec Records
...
COUNTY Z
Jan Records | Feb Records | Mar Records ... Dec Records
....
YEAR N
COUNTY A
Jan Records | Feb Records | Mar Records ... Dec Records
COUNTY B
Jan Records | Feb Records | Mar Records ... Dec Records
...
COUNTY Z
Jan Records | Feb Records | Mar Records ... Dec Records
Here are the tables that contain the data for this report.
COUNTY
county_cd
coname
RAW_PUR
year
county_cd
process_mt
The trouble is that the raw_pur table does not contain records for all
counties for all years. I do an outer join between the county and
raw_pur table. But that means that the counties that do not have
matching raw_pur records have a null value for the year field. So the
report groups these counties together into blank year group. I have a
'year' table that contains a single field with values for all the years
covering the range of available data. But if I just add that table with
another outer join won't I just get a 'year' record that has a null
value for the county name because there is nothing to join the year and
the county tables without a matching raw_pur record.
The database management systsm is Oracle 11g if that helps any.
Thanks in Advance
Ian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:314849
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
