Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: SQL query using elements from array

by mje (Curate)
on Oct 06, 2014 at 13:55 UTC ( #1102972=note: print w/replies, xml ) Need Help??


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!

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2019-04-23 20:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I am most likely to install a new module from CPAN if:
















    Results (118 votes). Check out past polls.

    Notices?