Andrea, It looks like this is an optimizer issue in Oracle - ie. that it is applying the TO_TIMESTAMP to all the records first, rather than just those that match the metadata_field_id.
I've tried rewriting the query so that it uses an inline view to obtain all the metadata records for the date accessioned field, and then apply the TO_TIMESTAMP to that... but bizarrely that doesn't seem to be working either. For now, I suggest that you separate out: SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned' As a distinct query, store the result in a variable, and then replace the: metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') with a "...metadata_field_id = " + variable + "..." That should at least make it work. File a bug report, and add list a reference to it under Bug Fixes / To Do here: http://wiki.dspace.org/index.php/Next_Release_Status and I'll address it properly for the 1.5 release when I check / add-in the Oracle support. G On Tue, 2007-09-11 at 10:32 +0000, Andrea Garrido Fernández wrote: > Sorry, I have realized that the query that doesn't work is : > > SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND > item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( > SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' > AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value), > 'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) > TO_DATE('2007-09-01', 'yyyy-MM-dd') AND > TO_TIMESTAMP( TO_CHAR(text_value), 'yyyy-mm-dd"T"hh24:mi:ss"Z"' ) < > TO_DATE('2007-10-21', 'yyyy-MM-dd') ) AND item_id IN ( SELECT item_id FROM > metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND > metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry > WHERE element = 'type' AND qualifier IS NULL) ) > > This query gives me error in dspace and in my sql+ worksheet. > > Sorry and thank yoy very much, > Andrea Garrido > > > > > > >From: Graham Triggs <[EMAIL PROTECTED]> > >To: Andrea Garrido Fernández <[EMAIL PROTECTED]> > >CC: dspace-tech@lists.sourceforge.net > >Subject: Re: [Dspace-tech] Problem with statistics > >Date: Tue, 11 Sep 2007 09:42:50 +0100 > > > >Hi, > > > >Well, the relevant part of this is the only thing that's not in > >English!! But the context is clear enough - it's having trouble parsing > >a valid date. > > > >Can you confirm that the start / end dates that you are passing to the > >script are of the form 'yyyy-mm-dd'. > > > >If your input is valid, then you've got a problem with one of the > >metadata value fields. You'll need to check the text_value of all the > >dc.date.accessioned entries (probably metadata_field_id=11). It looks > >like the offending item is of the correct format (yyyy-mm-ddThh:mi:ssZ), > >but contains invalid data. > > > >G > > > >On Mon, 2007-09-10 at 08:49 +0000, Andrea Garrido Fernández wrote: > > > Hi: > > > I have a problem when I try to execute the perl scripts for generating > > > statistics. > > > When I try to run the LogAnalyser class with a start or end date, I have > >an > > > error like this: > > > > > > java.sql.SQLException: ORA-01841: el valor (completo) del año debe > >estar > > > entre -4713 y +9999, y no debe ser igual a 0 > > > > > > at > > > > >oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) > > > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) > > > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) > > > at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745) > > > at > > > > >oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) > > > at > > > > >oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966) > > > at > > > > >oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062) > > > at > > > > >oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) > > > at > > > > >oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) > > > at > > > > >oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) > > > at > > > > >oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) > > > at > > > > >org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) > > > at > > > > >org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) > > > at > >org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:259) > > > at > > > > >org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:384) > > > at > > > > >org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:395) > > > at > >org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1264) > > > at > >org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:535) > > > at org.dspace.app.statistics.LogAnalyser.main(LogAnalyser.java:327) > > > > >------------------------------------------------------------------------------------------ > > > > > > I have tried many things, incluiding the solution given in this forum > > > before, using the REGEXP_REPLACE , but I find it impossible because > >oracle > > > don´t recognize this expression. > > > Colud yo help me, please? I am a bit lost. > > > Thanks, > > > Andrea > > > > > > _________________________________________________________________ > > > Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio > >con > > > MSN Entretenimiento. http://entretenimiento.msn.es/ > > > > > > > > > > >------------------------------------------------------------------------- > > > This SF.net email is sponsored by: Microsoft > > > Defy all challenges. Microsoft(R) Visual Studio 2005. > > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > > > _______________________________________________ > > > DSpace-tech mailing list > > > DSpace-tech@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/dspace-tech > >This email has been scanned by Postini. > >For more information please visit http://www.postini.com > > > > _________________________________________________________________ > Acepta el reto MSN Premium: Correos más divertidos con fotos y textos > increíbles en MSN Premium. Descárgalo y pruébalo 2 meses gratis. > http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_correosmasdivertidos > This email has been scanned by Postini. For more information please visit http://www.postini.com ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
