Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Oracle is null

  1. #1
    Join Date
    May 2007
    Posts
    22

    Default Oracle is null

    Hi Everyone.
    Here is the problem I have. The is a fact table that I am reading daily. So I need to identify the last record I read, and read from that point onwards. The problem is I will have to use many columns to identify a unique record so in some cases some of the columns can be null. So to get the position where I should read from I am planning to use all those fields in the where clause to get the row id. And read from that point onwards.

    To do the above and use oracle queries my query will look like,

    select rowid from table where column_name = ?

    Problem is in oracle you cant say column_name = null (you have to use column_name is null). But there is no way I can fit both these requirements in one query. What would be great is for me to be able to build a query based on the values of the selected columns. Could you please help me out.

    Thanks,
    Gavin

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    An automatic way for that is currently not in, and hopefully will also not be in there ever. It causes SQL cache blowout if you have too many options.

    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    247

    Default

    I hope I understood what you wanted to do, maybe this is a solution for your problem....

    You can use something like:
    Code:
    ... WHERE (column_name IS NULL OR column_name = ?)
    AND (column_name2 IS NULL OR column_name2 = ?) AND ...
    See attached transformation for an example - worked for me (you will have to change the database connection of course).

    Regards,
    Ben
    Attached Files Attached Files

  4. #4
    Join Date
    May 2007
    Posts
    22

    Default

    Hi Ben,
    Thanks I already tried that option but when you get a null value the
    column_name = ? part goes crazy and I don't get any rows. I am really stuck here. Would much appreciate any other suggestions.


    Thanks,
    Gavin

  5. #5
    Join Date
    Jul 2007
    Posts
    247

    Default

    Hmmm... strange, worked for me tough...

    Could you please attach your transformation? I'll take a look at it...


    Regards,
    Ben

  6. #6
    Join Date
    May 2007
    Posts
    22

    Default Oracle is null

    Hi Ben,
    Thanks here it is.

    Thanks,
    Gavin
    Attached Files Attached Files

  7. #7
    Join Date
    Jul 2007
    Posts
    247

    Default

    Since I don't have any of your data, is hard to tell but...
    1. Are you sure that INV_MIN is really NULL and does not contain any blanks or is even an empty string, but not null?
    2. Is it possible that INV_MAX and/or MONTH could be null as well?
    3. How many rows do you get by step "Get status"?
    4. Do you get any error messages?
    As I said before, the syntax you used it working for me. Nevertheless, here's another approach that should work as well:

    Try to set INV_MIN to a default value if the value is null. You could do this either in get status step or after the step using javascript. In the "Get last read row id" step you don't compare it to null but to the default value, using NVL(INV_MIN, 'your default value') = ?

    See the attached transformation for an example.

    Regards,
    Ben
    Attached Files Attached Files

  8. #8

    Default private

    Please by e-mail tcljava@gmail.com
    I am using with the Oracle. How he could decide this?




    09:19:17,984 INFO [JobStoreCMT] Recovering 0 jobs that were in-progress at the
    time of the last shut-down.
    09:19:17,984 INFO [JobStoreCMT] Recovery complete.
    09:19:18,000 INFO [JobStoreCMT] Removed 0 'complete' triggers.
    09:19:18,015 INFO [JobStoreCMT] Removed 0 stale fired job entries.
    09:19:18,015 INFO [QuartzScheduler] Scheduler PentahoQuartzScheduler_$_1 starte
    d.
    09:19:25,953 INFO [CWM] CWM - Loaded CWM model into the default repository.
    09:19:25,968 INFO [STDOUT] [pt_47] Pentaho BI Platform server is ready. (1.2.1-
    625 GA)
    09:19:26,656 INFO [TomcatDeployer] deploy, ctxPath=/sw-style, warUrl=.../tmp/de
    ploy/tmp31730pentaho.ear-contents/sw-style-exp.war/
    09:19:26,859 INFO [EARDeployer] Started J2EE application: file:/C:/JBoss/jboss-
    4.0.5.GA/server/default/deploy/pentaho.ear
    09:19:26,968 INFO [Http11BaseProtocol] Starting Coyote HTTP/1.1 on http-0.0.0.0
    -8080
    09:19:27,156 ERROR [STDERR] 2007/07/31 09:19:27:156 GMT-03:00 [INFO] ChannelSock
    et - JK: ajp13 listening on /0.0.0.0:8009
    09:19:27,234 INFO [JkMain] Jk running ID=0 time=0/94 config=null
    09:19:27,250 INFO [Server] JBoss (MX MicroKernel) [4.0.5.GA (build: CVSTag=Bran
    ch_4_0 date=200610162339)] Started in 1m:49s:360ms
    09:19:42,843 INFO [SolutionRepositoryBase] Solution Repository name not defined
    in Pentaho.xml node solution-repository/db-repository-name
    09:19:51,593 WARN [LoggerListener] Authentication event AuthenticationSuccessEv
    ent: luis; details: org.acegisecurity.ui.WebAuthenticationDetails@0: RemoteIpAdd
    ress: 127.0.0.1; SessionId: DBBFC2D200BCB0C6845061F641C35FE8
    09:19:51,640 WARN [LoggerListener] Authentication event InteractiveAuthenticati
    onSuccessEvent: luis; details: org.acegisecurity.ui.WebAuthenticationDetails@0:
    RemoteIpAddress: 127.0.0.1; SessionId: DBBFC2D200BCB0C6845061F641C35FE8
    09:19:58,218 INFO [SolutionRepositoryBase] SOLUTION-REPOSITORY: [pt_45] Adding
    action to solution repository clean_repository.xaction
    09:20:01,218 INFO [SolutionRepositoryBase] SOLUTION-REPOSITORY: [pt_45] Adding
    action to solution repository schedule-clean.xaction
    09:20:04,343 ERROR [CachedConnectionValve] Application error: /jsp/Admin.jsp did
    not complete its transaction
    09:20:04,656 ERROR [JTATransaction] JTA commit failed
    java.lang.IllegalStateException: No transaction.
    at org.jboss.tm.TxManager.commit(TxManager.java:246)
    at org.jboss.tm.usertx.client.ServerVMClientUserTransaction.commit(Serve
    rVMClientUserTransaction.java:140)
    at org.hibernate.transaction.JTATransaction.commit(JTATransaction.java:1
    46)
    at org.pentaho.repository.HibernateUtil.commitTransaction(HibernateUtil.
    java:372)
    at org.pentaho.repository.HibernateUtil.systemExitPoint(HibernateUtil.ja
    va:612)
    at org.pentaho.core.system.StandaloneApplicationContext.invokeExitPoints
    (StandaloneApplicationContext.java:132)
    at org.pentaho.core.system.PentahoSystem.systemExitPoint(PentahoSystem.j
    ava:1277)
    at org.pentaho.ui.servlet.GetImage.doPost(GetImage.java:137)
    at org.pentaho.ui.servlet.GetImage.doGet(GetImage.java:45)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:252)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:264)
    at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(Filt
    erSecurityInterceptor.java:107)
    at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(Fi
    lterSecurityInterceptor.java:72)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTra
    nslationFilter.java:110)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(S
    witchUserProcessingFilter.java:335)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.SecurityStartupFilter.doFilter(SecurityStartupFi
    lter.java:71)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFil
    ter(AnonymousProcessingFilter.java:125)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(R
    ememberMeProcessingFilter.java:142)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.RequestParameterAuthenticationFilter.doFilter(Re
    questParameterAuthenticationFilter.java:160)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicPr
    ocessingFilter.java:178)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProces
    singFilter.java:217)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:1
    08)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.HttpSessionReuseDetectionFilter.doFilter(HttpSes
    sionReuseDetectionFilter.java:142)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilte
    r(HttpSessionContextIntegrationFilter.java:193)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doF
    ilter(SecurityContextHolderAwareRequestFilter.java:81)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.jav
    a:148)
    at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.j
    ava:98)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.pentaho.core.system.SystemStatusFilter.doFilter(SystemStatusFilte
    r.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFi
    lter.java:96)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
    alve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
    alve.java:178)
    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(Securit
    yAssociationValve.java:175)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
    torBase.java:432)
    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValv
    e.java:74)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
    ava:126)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
    ava:105)
    at org.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConne
    ctionValve.java:156)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
    ve.java:107)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
    a:148)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
    :869)
    at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.p
    rocessConnection(Http11BaseProtocol.java:664)
    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo
    int.java:527)
    at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWor
    kerThread.java:112)
    at java.lang.Thread.run(Thread.java:595)
    09:20:04,687 ERROR [HibernateUtil] [pt_103] HIBUTIL.ERROR_0008 - [pt_53] commitT
    ransaction() failed.
    org.hibernate.TransactionException: JTA commit failed:
    at org.hibernate.transaction.JTATransaction.commit(JTATransaction.java:1
    53)
    at org.pentaho.repository.HibernateUtil.commitTransaction(HibernateUtil.
    java:372)
    at org.pentaho.repository.HibernateUtil.systemExitPoint(HibernateUtil.ja
    va:612)
    at org.pentaho.core.system.StandaloneApplicationContext.invokeExitPoints
    (StandaloneApplicationContext.java:132)
    at org.pentaho.core.system.PentahoSystem.systemExitPoint(PentahoSystem.j
    ava:1277)
    at org.pentaho.ui.servlet.GetImage.doPost(GetImage.java:137)
    at org.pentaho.ui.servlet.GetImage.doGet(GetImage.java:45)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:252)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:264)
    at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(Filt
    erSecurityInterceptor.java:107)
    at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(Fi
    lterSecurityInterceptor.java:72)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTra
    nslationFilter.java:110)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(S
    witchUserProcessingFilter.java:335)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.SecurityStartupFilter.doFilter(SecurityStartupFi
    lter.java:71)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFil
    ter(AnonymousProcessingFilter.java:125)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(R
    ememberMeProcessingFilter.java:142)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.RequestParameterAuthenticationFilter.doFilter(Re
    questParameterAuthenticationFilter.java:160)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicPr
    ocessingFilter.java:178)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProces
    singFilter.java:217)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:1
    08)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at com.pentaho.security.HttpSessionReuseDetectionFilter.doFilter(HttpSes
    sionReuseDetectionFilter.java:142)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilte
    r(HttpSessionContextIntegrationFilter.java:193)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doF
    ilter(SecurityContextHolderAwareRequestFilter.java:81)
    at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
    ilterChainProxy.java:274)
    at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.jav
    a:148)
    at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.j
    ava:98)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.pentaho.core.system.SystemStatusFilter.doFilter(SystemStatusFilte
    r.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFi
    lter.java:96)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
    icationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
    ilterChain.java:173)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
    alve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
    alve.java:178)
    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(Securit
    yAssociationValve.java:175)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
    torBase.java:432)
    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValv
    e.java:74)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
    ava:126)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
    ava:105)
    at org.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConne
    ctionValve.java:156)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
    ve.java:107)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
    a:148)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
    :869)
    at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.p
    rocessConnection(Http11BaseProtocol.java:664)
    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo
    int.java:527)
    at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWor
    kerThread.java:112)
    at java.lang.Thread.run(Thread.java:595)
    Caused by: java.lang.IllegalStateException: No transaction.
    at org.jboss.tm.TxManager.commit(TxManager.java:246)
    at org.jboss.tm.usertx.client.ServerVMClientUserTransaction.commit(Serve
    rVMClientUserTransaction.java:140)
    at org.hibernate.transaction.JTATransaction.commit(JTATransaction.java:1
    46)
    ... 63 more
    09:24:02,109 WARN [TransactionImpl] Transaction TransactionImpl:XidImpl[FormatI
    d=257, GlobalId=tecnol010/14, BranchQual=, localId=14] timed out. status=STATUS_
    ACTIVE

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.