hello,
i'm relatively new to using DBI. i'm attempting to execute the following
query against a MS SQL 2000 db. only the first instance of the results are
being returned. i'm not sure if i'm using the correct method and hope that
someone can point me in the right direction...
thanks,
-adam
part of code:
my $cust = 'ACD';
my $tradingpartner = 'IDX';
my $sql = "
declare [EMAIL PROTECTED] as varchar(10)
declare [EMAIL PROTECTED] as varchar(10)
declare [EMAIL PROTECTED] as varchar(75)
DECLARE CustomerCursor CURSOR STATIC READ_ONLY FOR
select distinct customer,multistateid,p.payorname from customer c join
customerpayor cp on c.custid = cp.custid
join payor p on p.payid = cp.payid
where cp.type = 'CLAIMCNT' and customer = ? and tradingpartner = ?
group by customer,multistateid,p.payorname
OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor
INTO [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED]
WHILE [EMAIL PROTECTED]@FETCH_STATUS = 0
BEGIN
select distinct p.multistateid,[EMAIL PROTECTED] as
PayorName,m.Type,pt.status,Description,testdate,productiondate,Completiondate,contingencydate
from Mandate M join Payor p on p.payid=m.payid
join payortxn pt on pt.payid= p.payid
where multistateid = [EMAIL PROTECTED] AND pt.type = 'ANSII' and ((m.type =
'AnsiClaims' and pt.txn = '837') or (m.type = 'AnsiRemits' and pt.txn =
'835'))
FETCH NEXT FROM CustomerCursor
INTO [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED]
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor
";
$sth = $dbh->prepare($sql);
$sth->execute($cust, $tradingpartner);
while( my @data = $sth->fetchrow_array)
{
print "@data\n";
}
results:
US01 AnsiClaims 4=Format Delivered ANSI X-12 Mandate (Enrollment Comm
Report Cl
aim Format) Enrollment= B Migration=B Testing=B Remit=A 2003-06-02
00:00:00.00
0 2003-08-15 00:00:00.000 2003-10-16 00:00:00.000
US01 AnsiRemits 7=Hold ANSI X-12 Mandate (Enrollment Comm Report Claim
Format)
Enrollment= B Migration=B Testing=B Remit=A 2003-04-01 00:00:00.000
2003-09-15
00:00:00.000 2003-10-16 00:00:00.000
should be:
US01 NULL AnsiClaims 4=Format Delivered ANSI X-12 Mandate (Enrollment
Comm
Report Claim Format) Enrollment= B Migration=B Testing=B Remit=A
2003-06-02 00:00:00.000 2003-08-15 00:00:00.000 2003-10-16 00:00:00.000
NULL
US01 NULL AnsiRemits 7=Hold ANSI X-12 Mandate (Enrollment Comm Report
Claim Format) Enrollment= B Migration=B Testing=B Remit=A 2003-04-01
00:00:00.000 2003-09-15 00:00:00.000 2003-10-16 00:00:00.000 NULL
US07 Nationwide Mutual Insurance Company AnsiClaims 6=Production ANSI
X-12
Mandate (Enrollment/Reports/Claims / Enrollment Type = A Migration Type =
B/Testing Type = D Remit Type = C 2002-04-16 00:00:00.000 2002-10-15
00:00:00.000 2003-10-16 00:00:00.000 NULL
US07 Nationwide Mutual Insurance Company AnsiRemits 6=Production ANSI
X-12
Mandate (Enrollment/Reports/Claims / Enrollment Type = A Migration Type =
B/Testing Type = D Remit Type = C 1900-01-01 00:00:00.000 2002-08-06
00:00:00.000 1900-01-01 00:00:00.000 NULL
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
