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
