Thank you, I´ve solved it!
The problem was that in LogAnalyser.java there were two if clauses in a
wrong order, i.e, I changed
in the method "public static Integer getNumItems(Context context, String
type)" these if clauses:
if (type != null)
{
query.append(" AND item_id IN ( " +
typeQuery + ") ");
}
if (startDate != null || endDate != null)
{
query.append(" AND item_id IN ( " +
dateQuery.toString() + ") ");
}
This is the right order, before "if (startDate != null || endDate != null)
{....." was first, so when you ran the query it gave the error because it
was looking for in an empty set (or something similar).
Thank yoy very much for your help, I will fix it as a bug.
Best regards,
Andrea
>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 12:37:22 +0100
>
>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
>
_________________________________________________________________
¿Estás pensando en cambiar de coche? Todas los modelos de serie y extras en
MSN Motor. http://motor.msn.es/researchcentre/
-------------------------------------------------------------------------
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
