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:
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','ZZZZ
+Z0006J','ZZZZZ0006P','ZZZZZ0007A')
AND AuditChrt_TimeStamp > ?
AND AuditChrt_TimeStamp < ?) AS A
UNION ALL
SELECT Login_ID, AuditMedication_TimeStamp AS TimeStamp, P
+atient_ID
FROM
(SELECT Login_ID, AuditMedication_TimeStamp, Patient_I
+D
FROM TopsData.dbo.AUDT_AuditMedication
WHERE Login_ID IN ('ZZZZZ0004R','ZZZZZ00050','ZZZZZ00
+06J','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 tim
+e
print join(", ", @row), "\n";
ERROR: DBD::ODBC::st failed: Conversion failed when converting data an
+d/or time from character string.
|