On Fri, 15 Nov 2002 10:31:51 -0500 Stephane Legault <[EMAIL PROTECTED]> wrote:
> I'm using DB2 with perl and I make this select in my code:
>
> $sth= $dbh->prepare ("select
> date(wedthreenregis),wenid_app,wealias,count(weduration) from
> slegault.weexectime where date(wedthreenregis)= ? and wenid_app= ? group
> by
> date(wedthreenregis),wenid_app,wealias");
>
> when the count(weduration) is 0 nothing print to the screen. I need that
> because I put the result in a array and make a graph from this array, so
> I
> need always the same format in the result of the query.
>
> Someone can help me?
I would not expect the SELECT to return anything when there is nothing
to count for a given date(wedthreenregis) + wenid_app combination.
If you must have the same set of date(wedthreenregis),wenid_app,wealias
values for all wenid_app values, I'd suggest collecting the entire set of
sets first and determining the range of values in Perl, then generating
your plots.
Example (not tested):
# _ALWAYS_ check for errors
$dbh -> {"RaiseError"} = 1;
my $sth = $dbh -> prepare( <<SQL );
SELECT wenid_app, DATE( wedthreenregis ), wenid_app, wealias,
COUNT( weduration )
FROM slegault.weexectime
WHERE wenid_app BETWEEN ? AND ?
AND DATE( wedthreenregis ) BETWEEN ? AND ?
GROUP BY wenid_app, DATE( wedthreenregis ), wenid_app, wealia
SQL
$sth -> execute( $lower_id, $upper_id, $lower_date, $upper_date );
my ( $id, $date, $app, $alias, $count, %count, %range );
$sth -> bind_columns( \( $id, $date, $app, $alias, $count ) );
while ( $sth -> fetch ) {
$count{$id}{$date}{$app}{$alias} = $count;
$range($app)($alias) = 1;
}
# Put 0 in where no value was returned
foreach $app ( keys %range ) {
foreach $alias ( keys %{$range{$app}} ) {
foreach $id ( keys %count ) {
foreach $date ( keys %{$count{$id}} ) {
$count{$id}{$date}{$app}{$alias} = 0
if ! defined $count{$id}{$date}{$app}{$alias};
}
}
}
}
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
