Two problems:
First, I tried:
my $measurementssth=$dbh->prepare("SELECT POLLENTRAP_ID, ALLER_ID, TYPE,
SPORES,FID, (SYSDATE-DATECOLLECTION) FROM $measurements_table WHERE
(SYSDATE-DATECOLLECTION) <15 ORDER by 6 LIMIT 1 ") ;
(trying to get the one line of data for which DATECOLLECTION IS closest to
SYSDATE PROVIDED these are no older than 14days, else I shoul get NULL)
and got:
DBD::Oracle::db prepare failed: ORA-00933: SQL command not properly ended (DBD
ERROR: error possibly near <*> indicator at char 154 in 'SELECT
POLLENTRAP_ID, ALLER_ID, TYPE,
SPORES,FID, (SYSDATE-DATECOLLECTION) FROM mysc.MEASUREMENTS WHERE
(SYSDATE-DATECOLLECTION) <15 ORDER by 6 <*>LIMIT 1 ') [for Statement
"SELECT POLLENTRAP_ID, ALLER_ID, TYPE,
SPORES,FID, (SYSDATE-DATECOLLECTION) FROM mysc.MEASUREMENTS WHERE
(SYSDATE-DATECOLLECTION) <15 ORDER by 6 LIMIT 1 "] at alarmchk.pl line 176.
IT looks like it does not like LIMIT. Is this not legal in Oracle? Surely
there must be a way to do this
**** SECOND PROBLEM ***
Another statement that fails is when I try to bind params
my $measurementssth2=$dbh->prepare("SELECT TYPE,
SPORES FROM $measurements_table WHERE (POLLENTRAP_ID = ? AND ALLER_ID=? AND
( TO_CHAR(SYSDATE,'IW')=
TO_CHAR(DATECOLLECTION,'IW') ) ) ");
and got:
DBD::Oracle::db prepare failed: ORA-00907: missing right parenthesis (DBD
ERROR: error possibly near <*> indicator at char 70 in 'SELECT TYPE,
SPORES FROM mysc.MEASUREMENTS WHERE ( POLLENTRAP_ID<*>=:p1 AND FID=:p2 AND
( TO_CHAR(SYSDATE,'IW')=
TO_CHAR(DATECOLLECTION,'IW') ) ') [for Statement "SELECT
TYPE,SPORES FROM mysc.MEASUREMENTS WHERE ( POLLENTRAP_ID=? AND FID=? AND
( TO_CHAR(SYSDATE,'IW')=
TO_CHAR(DATECOLLECTION,'IW') ) "] at alarmchk.pl line 180.
Again, is there something I am missing about bind params in Oracle?
