http://www.perlmonks.org?node_id=1102972


in reply to SQL query using elements from array

As an aside to the problem you are posting about (which is answered) why are you converting (with fn_sqlvarbasetostr) what looks like a timestamp to a string to compare with a string timestamp? Why not forget fn_sqlvarbasetostr and simply make your timestamps like "{ts '1998-05-02 01:23:56.123'}". You can omit the milliseconds.

  • Comment on Re: SQL query using elements from array

Replies are listed 'Best First'.
Re^2: SQL query using elements from array
by AllPaoTeam (Acolyte) on Oct 07, 2014 at 18:04 UTC
    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.
      try
      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'}"]);
      poj

      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.
Re^2: SQL query using elements from array
by AllPaoTeam (Acolyte) on Oct 06, 2014 at 18:15 UTC
    I am assuming this is in perl and not using the function within SQL Server? I can try it and see if it works. Thanks for the suggestion!