Re: simplify report generation by inserting bogus records with zero as value?

cannot you do an outer join?

mbradley wrote:

> I'm creating a database that tracks fuel use by facilities that
> generate electricity.
>
> In a table I call 'Generation,' there are columns for Plant ID, Fuel,
> Consumption (the number of fuel units consumed to generate the number
> of MWh in the 'Generation' field), and MWh(Megawatt Hours). The
> various columns in the Generation table establish relationships with
> other tables that allow me to (through joins) display generation and
> consumption summed by Plant, Plant Operator, State, SubRegion, and
> Region.
>
> The challenge is that, in many of the summations, it is desirable to
> display the totals by fuel and, for comparison purposes, return the
> same number of records for each entity by which the data is summed.
> So, for example, I want a query that returns summed data by state, but
> I want to make sure there is a state/fuel record for all fuels. If
> there is no use of a particular fuel in a particualr state (some
> states have no nuclear plants, most have no wind or solar powered
> plants), I still want a record for that fuel that corresponds to that
> state--but of course I want it have a zero value.
>
> I can think of a few different way to do this. One that I have tried
> involves rather elaborate UNION queries that return zero value records
> where no records for a particular summing entity/fuel combination
> exist. This works but is cumbersome (hard to maintain the queries,
> less efficient execution).
>
> An idea I had was to insert bogus records into the 'Generation' table
> for each potential summing entity (Plant Operator, State, SubRegion,
> Region)/fuel combination that would have a zero value in the 'MWh' and
> 'Consumption' columns. This would automatically generate a zero value
> for any summing entity/fuel combinations in aggregate queries where no
> other non-zero record exists, and wouldn't adversly effect queries
> where real records exist.
>
> The only downside to this approach I can think of are that I will have
> to write a fairly complex INSERT query to make sure I cover all
> possible combinations of entity/fuel if I want to make sure this will
> always work--but I only have to do this once. But, it just seems like
> it's not a good idea to have bogus records in the table (even though
> they would be named such that their purpose would be obvious).
>
> I'd appreciate opinions on the workability of this solution or
> suggested alternatives.
>
> Thanks in advance,
>
> ebradley4_at_yahoo.com

--
Anton Versteeg
DB2 Specialist
IBM Netherlands