in reply to Question about perl & oracle & and a SQL query

Crazy Hat:

I'd imagine that oracle has the getdate() function, as well as dateadd(), so you might just remove perl from the equation and use something like (untested):

my $sql1_datos = qq(
  select contador, cod_entidad_oim, cod_agr, cod_operacion 
  from mapcom_portext.TR0685002  
  where fch_hor_operacion between getdate() and dateadd(h,-1,getdate)
);

[download]
...roboticus

Replies are listed 'Best First'.
Re^2: Question about perl & oracle & and a SQL query
by jhourcle (Prior) on Feb 09, 2009 at 18:18 UTC

    You can do date arithmetic directly in Oracle, using days and fractional days. I don't deal with Oracle servers anymore, so this isn't tested, but the syntax should be:

      select contador, cod_entidad_oim, cod_agr, cod_operacion 
  from mapcom_portext.TR0685002 
  where fch_hor_operacion between sysdate() and (sysdate()-(1/24))

    [download]

    You can also use the 'interval' syntax:

      ... between sysdate() and ( sysdate() - interval '1' hour )
[reply]
[d/l]
[select]
Re^2: Question about perl & oracle & and a SQL query
by Sombrerero_loco (Beadle) on Feb 09, 2009 at 14:25 UTC
    Thanks too!! But i really dont know anything SQL in Oracle, and i prefeer to control how i run the query using perl (where i feel more skilled in it). But thanks anyway....another good response! :::When you dream, there're no rules, ppl can fly...anything can happen!!!:::
[reply]
      FWIW, it's preferable if you do 
        my $qry = qq(select contador, cod_entidad_oim, cod_agr, cod_operacio
+n from mapcom_portext.TR0685002  where fch_hor_operacion between to_d
+ate(?,'dd-mon-yyyy') and to_date(?,'dd-mon-yyyy'));

      [download]
      and later 
        my $sth = $dbh->prepare($qry);
  $sth->execute($now, $onehourago);

      [download]
      ... etc. (just in case)...
      []s, HTH, Massa (κς,πμ,πλ)
[reply]
[d/l]
[select]
        Thanks!!! This may be useful for me for another thin im planning, the use of variable when i execute the query.... Thanks a lot, very good answer!
[reply]

      If you want to get a now() out of Oracle, the only thing you need to know is to include the Oracle reserved word sysdate anywhere in your select statement.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom