Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re^2: SQL query using elements from array

by AllPaoTeam (Acolyte)
on Oct 07, 2014 at 18:04 UTC ( #1103099=note: print w/replies, xml ) Need Help??

in reply to Re: SQL query using elements from array
in thread SQL query using elements from array

How would I incorporate into the Perl code? Using code from McA, it tried the code below, but errors. I also tried using double quotes around it between the brackets. Below is the code and the error:
my @intervals = ([{ts'2012-07-01 00:00:00.000'},{ts'2012-07-02 00:00:0 +0.000'}], [{ts'2012-07-02 00:00:00.000'},{ts'2012-07-03 00:0 +0:00.000'}]); Error:"Bad Name after ts' at line 17" which is the line above.

Replies are listed 'Best First'.
Re^3: SQL query using elements from array
by poj (Abbot) on Oct 07, 2014 at 19:41 UTC
    my @intervals = ( ["{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'}"]);
Re^3: SQL query using elements from array
by mje (Curate) on Oct 08, 2014 at 09:35 UTC

    poj answered your problem above but I thought about this a bit more afterwards and realised there is a warning I should have made. The {ts 'xx'} is really an ODBC SQL escape sequence so it is really intended for your SQL. However, many drivers parse this in parameters (which you are using) as well.

      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.

        You can safely use a lot more parameteres than you are using. That error string does not look complete. It should contain an ODBC status code and a set of strings in square brackets showing what is reporting the error. Can we see the full string.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1103099]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2021-01-24 20:20 GMT
Find Nodes?
    Voting Booth?