Hi Monks!
I have this as part of my Perl program where I need to have these SQL queries done several times, I just don’t like how repetitive this code is, I can’t think of a better and more efficient way of doing this. Could there be a way to optimize the “connect” part at least or may be a more elegant way of doing this part of the code?

Thanks a lot!

my $db = "MyServer";
my $user = MyServerStuff::odbc->{ $db } { user };
my $pass = MyServerStuff::odbc->{ $db } { pass };


my $dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1})
+;
my $dbh_b = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1
+});
my $dbh2 = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1}
+);
my $dbh2_b = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 
+1});
my $dbh3 = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1}
+);
my $dbh3_b = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 
+1});
my $dbh4 = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1}
+);
my $dbh4_b = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 
+1});
my $dbh5 = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1}
+);
my $dbh5_b = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 
+1});


my $sql_s="select distinct main.type,
                                           history.date_forwarded,COUN
+T(1) as 'PPending'
          from main,history
                  where main.type='1'
                  and history.date_forwarded
                  between dateadd( day, -7, getdate()) and getdate()
                  group by history.date_forwarded,main.type";
                  
my $sql_sb="select distinct main.type,
                                             history.date_forwarded,CO
+UNT(1) as 'PPending'
                    from main,history
                    where main.type!='1'
                    and history.date_forwarded
                    between dateadd( day, -7, getdate()) and getdate()
                    group by history.date_forwarded,main.type";
                                                                      
+                                                            ";
my $sql_s2="select status_now, COUNT(1) as 'Got Total'
                    from main
                    where status_now='submitted' and type='1'
                    and submitted_date
                    between dateadd( day, -7, getdate()) and getdate()
                    group by status_now";
                    
my $sql_s2b="select status_now, COUNT(1) as 'Got Total'
                     from main
                    where status_now='submitted'and type!='1'
             and submitted_date
                     between dateadd( day, -7, getdate()) and getdate(
+)
                     group by status_now";
                     
my $sql_s3="select status_now, COUNT(1) as 'All_Completed'
                   from main
                   where status_now='completed'and type='1'
                   and submitted_date
               between dateadd( day, -7, getdate()) and getdate()
                    group by status_now";
                    
my $sql_s3b="select status_now, COUNT(1) as 'All_Completed'
                     from main
                     where status_now='completed'and type!='1'
                     and submitted_date
                between dateadd( day, -7, getdate()) and getdate()
                    group by status_now";
                    
my $sql_s4="select delete_check, COUNT(1) as 'Total Deleted'
                   from main
                   where delete_check='yes'and type='1'
                   and submitted_date
                   between dateadd( day, -7, getdate()) and getdate()
                   group by delete_check";
                   
my $sql_s4b="select delete_check, COUNT(1) as 'Total Deleted'
                     from main
                     where delete_check='yes'and type!='1'
                     and submitted_date
                     between dateadd( day, -7, getdate()) and getdate(
+)
                     group by delete_check";
                     
my $sql_s5="select status_now, COUNT(1) as 'Total Pending'
                    from main
                    where status_now='forwarded' and type='1'
                    and submitted_date
                    between dateadd( day, -7, getdate()) and getdate()
                    group by status_now";
                    
my $sql_s5b="select status_now, COUNT(1) as 'Total Pending'
                      from main
                      where status_now='forwarded' and type!='1'
                      and submitted_date
                      between dateadd( day, -7, getdate()) and getdate
+()
                      group by status_now";

my $sth = $dbh->prepare($sql_s);
$sth->execute() || die $sth->errstr;

my $sth_b = $dbh_b->prepare($sql_sb);
$sth_b->execute() || die $sth_b->errstr;

my $sth2 = $dbh2->prepare($sql_s2);
$sth2->execute() || die $sth2->errstr;

my $sth2_b = $dbh2_b->prepare($sql_s2b);
$sth2_b->execute() || die $sth2_b->errstr;

my $sth3 = $dbh3->prepare($sql_s3);
$sth3->execute() || die $sth3->errstr;

my $sth3_b = $dbh3_b->prepare($sql_s3b);
$sth3_b->execute() || die $sth3_b->errstr;

my $sth4 = $dbh4->prepare($sql_s4);
$sth4->execute() || die $sth4->errstr;

my $sth4_b = $dbh4_b->prepare($sql_s4b);
$sth4_b->execute() || die $sth4_b->errstr;

my $sth5 = $dbh5->prepare($sql_s5);
$sth5->execute() || die $sth5->errstr;

my $sth5_b = $dbh5_b->prepare($sql_s5b);
$sth5_b->execute() || die $sth5_b->errstr;


while ($pointer = $sth->fetchrow_hashref)
  {
                  $date_forwarded = $pointer->{'date_forwarded'};$date
+_forwarded=~s/\s+$//g;
                  $miss_request = $pointer->{'PPending'}; $miss_reques
+t=~s/\s+$//g;
                  push @g_total, $date_forwarded;
}


while ($pointer = $sth_b->fetchrow_hashref)
{
                     $c_date_forwarded = $pointer->{'date_forwarded'};
+$c_date_forwarded=~s/\s+$//g;
                     $c_pend_request = $pointer->{'PPending'}; $c_pend
+_request=~s/\s+$//g;
                     push @c_g_total, $c_date_forwarded;
}

while ($pointer = $sth2->fetchrow_hashref)
{
                     $status_now = $pointer->{'status_now'};$status_no
+w=~s/\s+$//g;
                     $total_received = $pointer->{'Got Total'};$total_
+received=~s/\s+$//g;
                     push @total_pending,$total_received;
}

while ($pointer = $sth2_b->fetchrow_hashref)
{
                     $c_status_now = $pointer->{'status_now'};$c_statu
+s_now=~s/\s+$//g;
                     $c_total_received = $pointer->{'Got Total'};$c_to
+tal_received=~s/\s+$//g;
                     push @c_total_pending,$c_total_received;
}


while ($pointer = $sth3->fetchrow_hashref)
{
                     $comp_status_now = $pointer->{'All_Completed'};$s
+tatus_now=~s/\s+$//g;
                     push @comp_status_now, $comp_status_now;
}

while ($pointer = $sth3_b->fetchrow_hashref)
{
                     $c_comp_status_now = $pointer->{'All_Completed'};
+$c_status_now=~s/\s+$//g;
                     push @c_comp_status_now, $c_comp_status_now;
}

while ($pointer = $sth4->fetchrow_hashref)
{
                     $delete_check = $pointer->{'Total Deleted'};$dele
+te_check=~s/\s+$//g;
                     push @delete_check, $delete_check;
}

while ($pointer = $sth4_b->fetchrow_hashref)
{
                     $c_delete_check = $pointer->{'Total Deleted'};$c_
+delete_check=~s/\s+$//g;
                     push @c_delete_check, $c_delete_check;
}

while ($pointer = $sth5->fetchrow_hashref)
{
                     $total_pending_status_now = $pointer->{'Total Pen
+ding'};$total_pending_status_now=~s/\s+$//g;
                     push @total_pending_status_now, $total_pending_st
+atus_now;
}

while ($pointer = $sth5_b->fetchrow_hashref)
{
                     $c_total_pending_status_now = $pointer->{'Total P
+ending'};$c_total_pending_status_now=~s/\s+$//g;
                     push @c_total_pending_status_now, $c_total_pendin
+g_status_now;
}
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.