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.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.