asked on

PERL is not sending all query request to SQL SERVER in While Loop

I have a perl app that is currently connected to a MYSQL Database, but we would like to migrate the app to a sql server database. A page in the app executes two queries, the 1st is the primary and the second gets its filter value from the 1st. The problem i'm having is, only the last row from the 1st query being executed in the second query. For instance if the 1st query returns the following
CrsNo |Customer | Response
723 Bill Urgent
724 Ken Urgent
725 Bob Urgent

The second query should query the database for each customer number that the first query provided, however, the only query that is executed is CrsNo 725. I have confirmed this with the sql profiler. The odd thing is the same code works fine with MYSql, I only run into this issue with sql server. Below is PERL Code and sql queries.
################## First Query

my $str = "select distinct q.CRSNo,q.CustomerName,q.ResponseAction,
                          from crs.tbl_customer q
                         where q.CustomerName = '$cname' order by q.CRSNo";
my $temp1 = $dbh->prepare("$str");

      $temp1->execute;
while (my @aray = $temp1->fetchrow_array)
{
($crsno,$crscust,$response) = @aray;

################Second Query
$str2 = "select q.DepartmentInitial,q.ReviewStatusName
                                      from crs.tbl_department as q
                                      where q.CRSNo ='$crsno' order by q.DepartmentNo";

$#dpt = -1;
      $#dptresp = -1;
      $temp2 = $dbh->prepare("$str2");
      $temp2->execute;

while (@row = $temp2->fetchrow_array)
{
($dptinit,$reviewstat) = @row;
                  $init = substr($reviewstat,0,1);
                  push @dpt,$dptinit;
                  push @dptresp,$init;
            }
$temp2->finish;
}
}

Vitor Montalvão

I'm not a PERL expert but why not do everything in a single query?

select q.DepartmentInitial,q.ReviewStatusName
from crs.tbl_department as q 
	inner join (select distinct CRSNo, CustomerName, ResponseAction,
		from crs.tbl_customer
		where CustomerName = '$cname') as s
	on q.CRSNo =s.CRSNo
order by q.DepartmentNo;

Open in new window

This why you'll only need the 2nd While.

kbennett13

ASKER

Because of the way it is presented to the page. The page will look like the the screen shot below. The second query supplies the data for the sub table. Below is the html for the page

      print "<tr>";
                  print "<td style='background-color: #FFFFFF' align='center' width='5%'>$crsno</td>";
                  print "<td style='background-color: #FFFFFF' align='left' width='15%'>$crscust</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='7%'>$response</td>";
                  print "<td style='background-color: #FFFFFF' align='left' width='32%'>";
                  print "<table border='1' cellspacing='0' width='100%'>";
                  print "<tr>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[0]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[1]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[2]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[3]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[4]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[5]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[6]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[7]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[8]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dpt[9]</td>";
                  print "</tr>";
                  print "<tr>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[0]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[1]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[2]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[3]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[4]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[5]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[6]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[7]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[8]</td>";
                  print "<td style='background-color: #FFFFFF' align='center' width='10%'>@dptresp[9]</td>";
                  print "</tr>";
                  print "</table>";
                  print "</td>";



                  print "</tr>";

kbennett13

ASKER

This is what i'm getting right now.

Vitor Montalvão

I still think that my suggestion will work for you. I've added the columns from the 1st query and joined them in a single one:

select q.DepartmentInitial,q.ReviewStatusName, q.CRSNo, s.CustomerName, s.ResponseAction
from crs.tbl_department as q 
	inner join (select distinct CRSNo, CustomerName, ResponseAction,
			from crs.tbl_customer
			where CustomerName = '$cname') as s
	on q.CRSNo =s.CRSNo
order by q.CRSNo, q.DepartmentNo;

Open in new window

I think it worth to you to give it a try.

kbennett13

ASKER

Well the problem with that technique is the second query returns multiple rows for each customer number. My report would no longer be accurate.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.