note
AllPaoTeam
Thanks guys, It seems to fail if I use it in a union where there are multiple select stmts. Is it because I am using too many bind variables? I am not sure why but here is the sql code that it fails on. All the other code is the same, only sql code is different, The ERROR I wrote on the bottom of the code:
<code>
my @intervals = (
["{ts'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}","{ts'2012-07-01 00:00:00.000'}", "{ts'2012-07-02 00:00:00.000'}"],
["{ts'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}","{ts'2012-07-02 00:00:00.000'}", "{ts'2012-07-03 00:00:00.000'}"]);
my $dbh = DBI-> connect('dbi:ODBC:DSN=WCPM-DB1;UID=TEST;PWD=pass') or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";
my $sql2 = <<EOSQL;
SELECT *, DATEDIFF(n,MIN_TIME,MAX_TIME) AS MINS_DIFF
FROM
(SELECT Login_ID, MIN(TimeStamp) AS MIN_TIME,MAX(TimeStamp) AS MAX_TIME
FROM
(SELECT Login_ID, AuditChrt_TimeStamp AS TimeStamp,
Patient_ID
FROM
(SELECT Login_ID, AuditChrt_TimeStamp, Patient_ID
FROM TopsData.dbo.AUDT_AuditChrt
WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZZ0006J','ZZZZZ0006P','ZZZZZ0007A')
AND AuditChrt_TimeStamp > ?
AND AuditChrt_TimeStamp < ?) AS A
UNION ALL
SELECT Login_ID, AuditMedication_TimeStamp AS TimeStamp, Patient_ID
FROM
(SELECT Login_ID, AuditMedication_TimeStamp, Patient_ID
FROM TopsData.dbo.AUDT_AuditMedication
WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZZ0006J','ZZZZZ0006P','ZZZZZ0007A')
AND AuditMedication_TimeStamp > ?
AND AuditMedication_TimeStamp < ?) AS B
) AS A
GROUP BY Login_ID) AS A;
EOSQL
my $sth = $dbh->prepare($sql2);
foreach my $interval (@intervals) {
$sth->execute(@$interval);
my @row;
while (@row = $sth->fetchrow_array) { # retrieve one row at a time
print join(", ", @row), "\n";
ERROR: DBD::ODBC::st failed: Conversion failed when converting data and/or time from character string.
</code>
1102757
1103148