in reply to DBI INTERVAL Error

DBI doesn't 'implement' SQL --- DBMS systems implement SQL. DBI just passes the SQL to the backend. That backend could be one of a whole range of DBMS systems (Oracle, Oracle MySql, Sybase, DB2, PostgreSQL, etc.).

INTERVAL is in the SQL standard but that doesn't mean automatically that all systems implement it.

Which backend (or backends) are you using?

UPDATE: Oh wait, the error says: 'DBD::CSV'. So, reading files, which means no INTERVAL I don't think.

It seems DBD::CSV implements this SQL ("a small but useful subset"): SQL::Statement

(and no, I don't see INTERVAL in there.)

Replies are listed 'Best First'.
Re^2: DBI INTERVAL Error
by Anonymous Monk on Mar 20, 2016 at 16:51 UTC
    Hi, I am not using any backend DB, I am open a bunch of .csv files and using DBI to create reports.
    And as part of my SQL query is to use 
    my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
         f_dir   => "mkdir",
         f_ext   => ".csv",
         f_enc   => "utf-8",
         csv_eol => "\n",
         RaiseError => 1,
       });
...
WHERE mydate>= (CURRENT_DATE() - interval(6) DAY)
...

    [download]
    Any other alternatives?
    Thanks!
[reply]
[d/l]
      Any other alternatives? 
      #!perl
use strict;
use DBI;
use Time::Piece;
use Time::Seconds 'ONE_DAY';

my $t = Time::Piece->new();
my $t1 = $t - 6 * ONE_DAY;
my $t2 = $t + 1 * ONE_DAY;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
  f_dir   => "mkdir",
  f_ext   => ".csv",
  f_enc   => "utf-8",
  csv_eol => "\n",
  RaiseError => 1,
});

my $sql = "SELECT name,city, COUNT(*) AS rows 
           FROM file.csv
           WHERE (mydate >= ?) AND (mydate < ?)
           GROUP BY name,city";

my $sth = $dbh->prepare($sql);
$sth->execute($t1->ymd,$t2->ymd);

my $ar = $sth->fetchall_arrayref;
print join ",",@$_,"\n" for @$ar;

      [download]
      poj
[reply]
[d/l]
        Why wouldn't this work with the code sample you posted? 
        my $sql = " SELECT * 
              FROM
                 ( SELECT name, city, COUNT(*) AS Rows 
                  FROM file.csv
                  WHERE ( mydate >= ? ) 
                  AND  ( mydate   < ? ) ) t1 
              INNER JOIN
                 ( SELECT name, city, COUNT(*) AS Rows 
                   FROM file.csv 
                   WHERE ( mydate >= ? )
                  AND ( mydate < ? ) ) t2 
              ON t1.mydate = t2.mydate";

        [download]
        From here:
        http://search.cpan.org/~jzucker/SQL-Statement-1.14/lib/SQL/Statement/Syntax.pod

        Docs:
        Explict Join Qualifiers: NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
        Any thoughts?
[reply]
[d/l]

In Section Seekers of Perl Wisdom