I'm guessing line 949 mentioned in the error is
'while( my @data = $sth->fetchrow_array)'
What's happening is this:
1. First definition of $sth as the main query.
2. Get first row at ln 949.
3. Later on, you define $sth twice, using $sql2 and $sql3. The discards the
original $sth. These are not inner queries in the traditional sense (INNER
JOIN), they're just inner to the loop.
4. Finish first run of while loop, go back to ln 949.
5. The new $sth has passed out of scope, and 'no select statement is
currently executing'.
The fix would be to rename the $sth inside the loop, or rescope it using
'my' in the line:
'$sth = $dbh->prepare($sql2);'
J-T MacNeil
Design Team Member, Connect 24 Division
Digital Security Controls
Phone: (905) 760-3000 x7318
e-Mail: [EMAIL PROTECTED]
> -----Original Message-----
> From: Adam Peterson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 30, 2003 11:52 AM
> To: [EMAIL PROTECTED]
> Subject: Help With inner queries
> Importance: Low
>
>
> hello,
>
> i'm having trouble with inner queries and am wondering if someone can
> offer help? i've confirmed that the actual sql is correct -
> or at least it
> works from MS SQL Query Analyzer. the code also works when
> there is only
> one row, but errors out when there is more than one row returned.
>
> thanks for any help,
> -adam
>
>
> here is the error:
> DBD::ODBC::st fetchrow_array failed: (DBD: no select
> statement currently
> executi
> ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 949.
>
> here is my code:
>
> sub sql_custtracker
> {
>
> my @list = ();
> my $color = "";
> $cnt = 1;
> my $where = "";
> $where = $_[0];
> my $sql = "";
> my $sql2 = "";
> my $sql3 = "";
> my $group = "";
> my $payorid = "";
> my $multistateid = "";
> my $customername = "";
>
> #if ($where =~ /\w+/)
> if ($where)
> {
> $sql = "
>
> select txnid,
> datestarted,
> ISNULL(projectlead, ' ') as projectlead,
> ISNULL(status, ' ') as status,
> ISNULL(biller, ' ') as biller,
> ISNULL(formatstatus, ' ') as formatstatus,
> ISNULL(unionid, '0') as unionid,
> ISNULL(customercase, ' ') as customercase,
> ISNULL(enrollmentstatus, ' ') as
> enrollmentstatus,
> ISNULL(routestatus, ' ') as routestatus,
> ISNULL(complianceerrors, ' ') as
> complianceerrors,
> mandateid,
> custid,
> ISNULL(formatname, ' ') as formatname,
> expectedstartdate,
> expectedcompdate,
> ISNULL(testfilename, ' ') as testfilename,
> testfiledate,
> ISNULL(trainingstatus, ' ') as
> trainingstatus,
> ISNULL(kickoffstatus, ' ') as
> kickoffstatus,
> ISNULL(websitestatus, ' ') as
> websitestatus,
> ISNULL(paymentverification, ' ')
> as paymentverification,
> ISNULL(productioncontrol, ' ') as
> productioncontrol,
> ISNULL(fileacknowledged, ' ') as
> fileacknowledged,
> ISNULL(customercontact, ' ') as
> customercontact
> from customertracker
> $where
> ";
> }
> else
> {
> $sql = "
>
> select txnid,
> datestarted,
> ISNULL(projectlead, ' ') as projectlead,
> ISNULL(status, ' ') as status,
> ISNULL(biller, ' ') as biller,
> ISNULL(formatstatus, ' ') as formatstatus,
> ISNULL(unionid, '0') as unionid,
> ISNULL(customercase, ' ') as customercase,
> ISNULL(enrollmentstatus, ' ') as
> enrollmentstatus,
> ISNULL(routestatus, ' ') as routestatus,
> ISNULL(complianceerrors, ' ') as
> complianceerrors,
> mandateid,
> custid,
> ISNULL(formatname, ' ') as formatname,
> expectedstartdate,
> expectedcompdate,
> ISNULL(testfilename, ' ') as testfilename,
> testfiledate,
> ISNULL(trainingstatus, ' ') as
> trainingstatus,
> ISNULL(kickoffstatus, ' ') as
> kickoffstatus,
> ISNULL(websitestatus, ' ') as
> websitestatus,
> ISNULL(paymentverification, ' ')
> as paymentverification,
> ISNULL(productioncontrol, ' ') as
> productioncontrol,
> ISNULL(fileacknowledged, ' ') as
> fileacknowledged,
> ISNULL(customercontact, ' ') as
> customercontact
> from customertracker
> ";
> }
>
> print FILE "$sql\n";
>
> $sth = $dbh->prepare($sql);
> $sth->execute();
>
> while( my @data = $sth->fetchrow_array)
> {
> my $txnid = $data[0];
> my $datestarted = $data[1];
> my $projectlead = $data[2];
> print "$projectlead\n";
> my $status = $data[3];
> my $biller = $data[4];
> my $formatstatus = $data[5];
> my $unionid = $data[6];
> my $customercase = $data[7];
> my $enrollmentstatus = $data[8];
> my $routestatus = $data[9];
> my $complianceerrors = $data[10];
> my $mandateid = $data[11];
> my $custid = $data[12];
> my $formatname = $data[13];
> my $expectedstartdate = $data[14];
> my $expectedcompdate = $data[15];
> my $testfilename = $data[16];
> my $testfiledate = $data[17];
> my $trainingstatus = $data[18];
> my $kickoffstatus = $data[19];
> my $websitestatus = $data[20];
> my $paymentverification = $data[21];
> my $productioncontrol = $data[22];
> my $fileacknowledged = $data[23];
> my $customercontact = $data[24];
>
> ###inner queries for custid, payorid, and multistate
>
> $sql2 = "select [group], customer from customer
> where custid =
> '$custid'";
>
> $sth = $dbh->prepare($sql2);
> $sth->execute();
>
> while( my @row = $sth->fetchrow_array)
> {
> $group = $row[0];
> print "$group\n";
> $customername = $row[1];
> print "$customername\n";
> }
>
> $sql3 = "select payorid, multistateid from
> payor where payid = (select
> payid from payortxn where mandateid = '$mandateid')";
>
> $sth = $dbh->prepare($sql3);
> $sth->execute();
>
> while( my @row = $sth->fetchrow_array)
> {
> $payorid = $row[0];
> print "$payorid\n";
>
> $multistateid = $row[1];
> print "$multistateid\n";
> }
>
> $multistateid = "<a
> href=http://eggpayorteam/cgi-bin/payorlist.cgi?msid="; .
> $multistateid .
> "&stat=payor>" . $multistateid . "</a>";
>
> if ($cnt%2 == 0)
> {
> $color = "bgcolor=#eeeecc";
> }
> else
> {
> $color = "bgcolor=#ffffff";
> }
>
>
>
> my %rows = ( num=>$cnt,
> color=>$color,
> datestarted=>$datestarted,
> projectlead=>$projectlead,
> status=>$status,
> biller=>$biller,
> formatstatus=>$formatstatus,
> unionid=>$unionid,
> customercase=>$customercase,
> enrollmentstatus=>$enrollmentstatus,
> routestatus=>$routestatus,
> complianceerrors=>$complianceerrors,
> formatname=>$formatname,
> expectedstartdate=>$expectedstartdate,
> expectedcompdate=>$expectedcompdate,
> custid=>$customername,
> group=>$group,
> payorid=>$payorid,
> multistate=>$multistateid,
> testfilename=>$testfilename,
> testfiledate=>$testfiledate,
> trainingstatus=>$trainingstatus,
> kickoff=>$kickoffstatus,
> websitestatus=>$websitestatus,
> customercontact=>$customercontact,
> );
>
>
>
> push (@list, \%rows);
>
>
>
>
>
> $cnt++;
>
> }
>
> return @list;
>
> }
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
