Shaunn Johnson wrote at Fri, 26 Jul 2002 17:35:30 +0200:
> Howdy:
>
> ...
> Suggestions? Comments (like, 'you can make the much smaller by doing ...)? Thanks
>in advance!
Year, you really can.
> [snip code]
> my $sql = qq| select count (type)
> from sys_dates
> where
> type = 'AC' or
> type = 'AI' or
> type = 'AM' or
> type = 'AP' or
[ and so on and so and so]
> type = 'YD'
> group by type
> | ;
That seems awkward.
First the select command is perhaps slower than it needn't.
In MySQL (I don't know PostgreSQL),
there's an in statement,
so you could type an sql statement like
SELECT COUNT(type) FROM sys_dates WHERE type IN ('AC', 'AI', ... )
However, I still wouldn't suggest to write all the 'AC', 'AI', ... out.
Just put them into an array
my @accepted_types = qw(
AC AI AM AP AS BB BD BR CA
CE CP CV DC DI DM EA EC ED EY FE H2 H3 HA HB HI
I3 IA IC ID IN IO LD MA MB MC MD ME MN MU OD PE
PV RO RU SE TE TH TM TR WA WV YA YC YD
);
my $type_str = join ", " => map "'$_'", @accepted_types;
my $sql = "SELECT COUNT(type) FROM sys_dates WHERE type IN ($type_str)";
>
> #
> # test the sql and prepare to use
> #
>
> my $sth=$dbh->prepare($sql) or die "Error =", DBI::errstr; unless ($sth->execute) {
> print"\n\tExecute failed for stmt:\n\t$sql\nError = ", DBI::errstr;
>$sth->finish;
> $dbh->disconnect;
> die "\n\t\tClean up finished\n";
> }
> }
> while (@count)=$sth->fetchrow) {
> $dbh->do("insert into t_sysdates_trend ("AC", "AI", "AM", "AP", "AS", "BB", "BD",
>"BR", "CA",
> "CE", "CP", "CV", "DC", "DI", "DM", "EA", "EC", "ED", "EY", "FE", "H2", "H3", "HA",
>"HB", "HI",
> "I3", "IA", "IC", "ID", "IN", "IO", "LD", "MA", "MB", "MC", "MD", "ME", "MN", "MU",
>"OD", "PE",
> "PV", "RO", "RU", "SE", "TE", "TH", "TM", "TR", "WA", "WV", "YA", "YC", "YD",
>"load_date") values
> ( $count[0], $count[1], $count[2], $count[3], $count[4], $count[5], $count[6],
>$count[7],
> $count[8], $count[9], $count[10], $count[11], $count[12], $count[13], $count[14],
>$count[15],
> $count[16], $count[17], $count[18], $count[19], $count[20], $count[21], $count[22],
>$count[23],
> $count[24], $count[25], $count[26], $count[27], $count[28], $count[29], $count[30],
>$count[31],
> $count[32], $count[33], $count[34], $count[35], $count[36], $count[37], $count[38],
>$count[39],
> $count[40], $count[41], $count[42], $count[43], $count[44], $count[45], $count[46],
>$count[47],
> $count[48], timestamp(now()) )" );
The same like above to the types,
and I wouldn't write $count[0], ... $count[48];
It's simpler written as
@count
or if the count array is greater than 48,
but you only need the first 49 items then it can be written as
@count[0..48]
Best Wishes,
Janek
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
