Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Date problems

  1. #1
    Join Date
    Jan 2006
    Posts
    313

    Default Date problems

    On DB2/400 there is exist different datetime database formats. You have Date, timestamp,time each requiring the correct syntax in your SQL
    A database field Type timestamp requires in the SQL a value in the form of "yyyy-mm-tt-hh.mm.ss.tttttt", while a database field of type Date requires a SQL value of the form "yyyy-mm-dd"

    The metadata editor does not know the type timestamp and only knows the type date. When importing a table with a record type timestamp, it is classified as type Date.
    In the MQL query builder you can get the query to work as long as the syntax af a date in the conditions section, is of the form "yyyy-mm-tt-hh.mm.ss.tttttt".

    When you upload the model to adhoc and try to use the date field in the conditions, you will see that the date picker enters a date in the "yyyy-mm-dd" format. Executing this gives a "[SQL0180] Syntax of date, time, or timestamp value not valid." error.

    To get around this i have converted the timestamp to a date as follows. I have overrided the value (T_CDAT) for formula to 'DATE( "History Cycle Counting / Adjustment order lines"."T_CDAT")' and set the "Is formula exact" flag to checked.

    This works fine inside the metadata MQL query builder. When you run a query in adhoc that references the dae field in the conditions section, the report finishes, the data is correct, but your log file is full of error messages.

    Code:
    2008-02-18 17:17:20,296 INFO  [STDOUT] 17:17:20,296 ERROR [PMSFormula] an exception occurred
    org.jfree.formula.parser.ParseException: Lexical error at line 1, column 57.  Encountered: "T" (84), after : "."
            at org.jfree.formula.Formula.<init>(Formula.java:63)
            at org.pentaho.pms.schema.PMSFormula.parseAndValidate(PMSFormula.java:225)
            at org.pentaho.pms.schema.BusinessColumn.getFunctionTableAndColumnForSQL(BusinessColumn.java:231)
            at org.pentaho.pms.schema.BusinessModel.getSQL(BusinessModel.java:823)
            at org.pentaho.pms.schema.BusinessModel.getSQL(BusinessModel.java:769)
            at org.pentaho.pms.mql.MQLQuery.getQuery(MQLQuery.java:194)
            at org.pentaho.plugin.mql.MQLRelationalDataComponent.getQuery(Unknown Source)
            at org.pentaho.plugin.sql.SQLBaseComponent.executeAction(Unknown Source)
            at org.pentaho.plugin.mql.MQLRelationalDataComponent.executeAction(Unknown Source)
            at org.pentaho.plugin.ComponentBase.execute(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeComponent(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeAction(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.performActions(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeLoop(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeSequence(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeSequence(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.executeInternal(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.execute(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.execute(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.executeActionSequence(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.generatePreviewAsStream(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.generatePreview(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.dispatch(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.doGet(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.doPost(Unknown Source)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
            at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
            at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.SecurityStartupFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.RequestParameterAuthenticationFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:178)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:108)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.HttpSessionReuseDetectionFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:193)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
            at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.pentaho.core.system.SystemStatusFilter.doFilter(Unknown Source)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.pentaho.ui.servlet.SetCharacterEncodingFilter.doFilter(Unknown Source)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
            at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
            at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
            at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
            at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
            at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
            at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
            at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
            at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
            at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
            at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
            at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
            at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
            at java.lang.Thread.run(Thread.java:595)
    2008-02-18 17:17:20,312 INFO  [STDOUT] 17:17:20,296 ERROR [BusinessColumn] BusinessColumn.ERROR_0001 - Failed to Parse Formula DATE( "History Cycle Counting / Adjustment order lines".T_TRDT)
    org.pentaho.pms.core.exception.PentahoMetadataException: PMSFormula.ERROR_0005 - Failed to parse formula DATE( "History Cycle Counting / Adjustment order lines".T_TRDT)
            at org.pentaho.pms.schema.PMSFormula.parseAndValidate(PMSFormula.java:233)
            at org.pentaho.pms.schema.BusinessColumn.getFunctionTableAndColumnForSQL(BusinessColumn.java:231)
            at org.pentaho.pms.schema.BusinessModel.getSQL(BusinessModel.java:823)
            at org.pentaho.pms.schema.BusinessModel.getSQL(BusinessModel.java:769)
            at org.pentaho.pms.mql.MQLQuery.getQuery(MQLQuery.java:194)
            at org.pentaho.plugin.mql.MQLRelationalDataComponent.getQuery(Unknown Source)
            at org.pentaho.plugin.sql.SQLBaseComponent.executeAction(Unknown Source)
            at org.pentaho.plugin.mql.MQLRelationalDataComponent.executeAction(Unknown Source)
            at org.pentaho.plugin.ComponentBase.execute(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeComponent(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeAction(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.performActions(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeLoop(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeSequence(Unknown Source)
            at org.pentaho.core.runtime.RuntimeContext.executeSequence(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.executeInternal(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.execute(Unknown Source)
            at org.pentaho.core.solution.SolutionEngine.execute(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.executeActionSequence(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.generatePreviewAsStream(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.generatePreview(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.dispatch(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.doGet(Unknown Source)
            at org.pentaho.ui.servlet.AdhocWebService.doPost(Unknown Source)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
            at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
            at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
            at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.SecurityStartupFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.RequestParameterAuthenticationFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:178)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:108)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at com.pentaho.security.HttpSessionReuseDetectionFilter.doFilter(Unknown Source)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:193)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81)
            at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
            at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
            at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.pentaho.core.system.SystemStatusFilter.doFilter(Unknown Source)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.pentaho.ui.servlet.SetCharacterEncodingFilter.doFilter(Unknown Source)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
            at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
            at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
            at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
            at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
            at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
            at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
            at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
            at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
            at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
            at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
            at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
            at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
            at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
            at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
            at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
            at java.lang.Thread.run(Thread.java:595)
    2008-02-18 17:17:20,328 INFO  [STDOUT] 17:17:20,328 ERROR [PMSFormula] an exception occurred
    What happens is that PMSFormula wants to parse the "date". This date should not be parsed, but left as it as it is a part of the SQL we want to send to the database.
    How can we format the value so that PMSFormula knows that this value should not be parsed.

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Your formula is not valid according to the syntax of the OpenFormula specifications.

    If you want to read a value from a field, then the fieldname has to be put in square brackets. I'm not sure whether the resultset the meta-data system sees actually contains the fieldname with the very same quoting style.

    If it does, then you will have to properly escape the quotes by doubling the double-quotes. (However, if you try this, you will run into PRE-257 (Escape processing of embedded double-quotes is invalid)).

    So once PRE-257 is fixed, this would be the correct formula:

    Code:
    DATE( ["History Cycle Counting / Adjustment order lines"".""T_CDAT"])
    (The square brackets have the same meaning as the star in C - the string itself is just a name, only the square brackets actually convert the string into a referenceable field. A overview over the formula syntax can be found here: http://wiki.pentaho.org/display/Repo...la+Expressions )
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jan 2006
    Posts
    313

    Default Some more confusion

    Thomas,

    Are you sure of the []?

    When i do this, i see in the MQL query builder this when i preview teh SQL
    Code:
         ,DATE( ["History Cycle Counting / Adjustment order lines"".""T_CDAT"]) AS COL8
    What i need is
    Code:
         ,DATE( "History Cycle Counting / Adjustment order lines"."T_CDAT") AS COL8
    Without []

    I get lost because in the MQL builder the preview is working and when i try in adhoc reporting i get different messages. It seems i cannot get it to work in both.... This is very confusing. I tried updating libformula to version 0.1.15 but it does not change anything to the different behaviour.

    Also i think there is a problem related to the fact that the word DATE is recognized by libformula and libformula tries to expand it, but actually DATE is needed as is in the SQL and libformula should not touch it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.