3 Replies Latest reply on Jan 11, 2008 9:23 AM by Arnie Morein

    How to do a Max in a sub query...

    Arnie Morein Newbie

      I need to select records that are linked together based on two parameters, one in table a the other in table b. The named query below is the item in question.

       <class name="us.tx.state.oag.olrap.db.InfoApplication" table="info_application">
      
       <id name="applicationId" type="java.math.BigDecimal">
       <column name="application_id" precision="18" scale="0" />
       <generator class="assigned" />
       </id>
       <many-to-one name="codeFiscalYear" class="us.tx.state.oag.olrap.db.CodeFiscalYear"
       fetch="select" lazy="false">
       <column name="fiscal_year_id" precision="18" scale="0" not-null="true" />
       </many-to-one>
       <many-to-one name="codeEvalStatus" class="us.tx.state.oag.olrap.db.CodeEvalStatus"
       fetch="select" lazy="false">
       <column name="evaluation_id" precision="18" scale="0" not-null="true" />
       </many-to-one>
       <many-to-one name="codeStatus" class="us.tx.state.oag.olrap.db.CodeStatus"
       fetch="select" lazy="false">
       <column name="status_id" precision="18" scale="0" not-null="true" />
       </many-to-one>
       <property name="baseAward" type="java.math.BigDecimal">
       <column name="base_award" precision="11" not-null="true" />
       </property>
       <property name="closedDate" type="java.sql.Timestamp">
       <column name="closed_date" length="23" />
       </property>
       <property name="createdDate" type="java.sql.Timestamp">
       <column name="created_date" length="23" not-null="true" />
       </property>
       <property name="emplid" type="java.lang.String">
       <column name="emplid" length="11" not-null="true" />
       </property>
       <property name="hireDate" type="java.sql.Timestamp">
       <column name="hire_date" length="23" not-null="true" />
       </property>
       <property name="jobCode" type="java.lang.String">
       <column name="job_code" length="6" not-null="true" />
       </property>
       <property name="lastEvalDate" type="java.sql.Timestamp">
       <column name="last_eval_date" length="23" />
       </property>
       <property name="monthsOfService" type="java.math.BigDecimal">
       <column name="months_of_service" precision="4" scale="0" not-null="true" />
       </property>
       <property name="okWithBar" type="java.lang.Integer">
       <column name="ok_with_bar" not-null="true" />
       </property>
       <property name="submittedDate" type="java.sql.Timestamp">
       <column name="submitted_date" length="23" not-null="false" />
       </property>
       <property name="calculatedAward" type="java.math.BigDecimal">
       <column name="calculated_award" precision="11" not-null="true" />
       </property>
       <property name="actualAward" type="java.math.BigDecimal">
       <column name="actual_award" precision="18" not-null="true" />
       </property>
       <property name="awardDate" type="java.sql.Timestamp">
       <column name="award_date" length="23" />
       </property>
       <property name="crWho" type="java.lang.String">
       <column name="cr_who" length="20" not-null="true" />
       </property>
       <property name="crWhen" type="java.sql.Timestamp">
       <column name="cr_when" length="23" not-null="true" />
       </property>
       <property name="upWho" type="java.lang.String">
       <column name="up_who" length="20" not-null="true" />
       </property>
       <property name="upWhen" type="java.sql.Timestamp">
       <column name="up_when" length="23" not-null="true" />
       </property>
       <set name="infoLogs" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
       <key>
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </key>
       <one-to-many class="us.tx.state.oag.olrap.db.InfoLog" />
       </set>
       <set name="infoLenders" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
       <key>
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </key>
       <one-to-many class="us.tx.state.oag.olrap.db.InfoLender" />
       </set>
       <set name="infoWorkflows" inverse="true" lazy="false" fetch="select" cascade="all-delete-orphan">
       <key>
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </key>
       <one-to-many class="us.tx.state.oag.olrap.db.InfoWorkflow" />
       </set>
       <set name="infoLoans" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
       <key>
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </key>
       <one-to-many class="us.tx.state.oag.olrap.db.InfoLoan" />
       </set>
       <set name="infoEmployees" inverse="true" lazy="false" fetch="select" cascade="all-delete-orphan" >
       <key>
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </key>
       <one-to-many class="us.tx.state.oag.olrap.db.InfoEmployee"/>
       </set>
       </class>
      
       <query name="ApplicationsByHrEmployee">
       from us.tx.state.oag.olrap.db.InfoApplication as apps
       where apps.codeStatus.statusId = :codeStatus
       and apps.infoWorkflows.emplid = :emplId
       order by apps.createdDate asc
       </query>
      


      The workflow definition is:

      <?xml version="1.0" encoding="utf-8"?>
      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
      <!--
       Mapping file autogenerated by MyEclipse Persistence Tools
      -->
      <hibernate-mapping>
       <class name="us.tx.state.oag.olrap.db.InfoWorkflow" table="info_workflow">
       <id name="workflowId" type="java.math.BigDecimal">
       <column name="workflow_id" precision="18" scale="0" />
       <generator class="assigned" />
       </id>
       <many-to-one name="infoApplication" class="us.tx.state.oag.olrap.db.InfoApplication"
       fetch="select" lazy="proxy">
       <column name="application_id" precision="18" scale="0" not-null="true" />
       </many-to-one>
       <many-to-one name="codeStatus" class="us.tx.state.oag.olrap.db.CodeStatus"
       fetch="select" lazy="false">
       <column name="status_id" precision="18" scale="0" not-null="true" />
       </many-to-one>
       <property name="commentary" type="java.lang.String">
       <column name="commentary" not-null="true" />
       </property>
       <property name="emplid" type="java.lang.String">
       <column name="emplid" length="11" not-null="true" />
       </property>
       <property name="entryDate" type="java.sql.Timestamp">
       <column name="entry_date" length="23" not-null="true" />
       </property>
       <property name="crWho" type="java.lang.String">
       <column name="cr_who" length="20" not-null="true" />
       </property>
       <property name="crWhen" type="java.sql.Timestamp">
       <column name="cr_when" length="23" not-null="true" />
       </property>
       <property name="upWho" type="java.lang.String">
       <column name="up_who" length="20" not-null="true" />
       </property>
       <property name="upWhen" type="java.sql.Timestamp">
       <column name="up_when" length="23" not-null="true" />
       </property>
       </class>
      
       <sql-query name="getCurrentWorkflowForApplication" callable="true">
       <return alias="workflow" class="us.tx.state.oag.olrap.db.InfoWorkflow"/>
       { call get_crnt_wkfl_for_app_spr(?) }
       </sql-query>
      </hibernate-mapping>
      



      The problem is, the named query needs to include a max() on entry_date from the workflow table, basically, this the SQL I need:

      select * from info_application a, info_workflow b
      where a.application_id = b.application_id
      and b.entry_date = (select max(entry_date) from info_workflow c
       where a.application_id = c.application_id)
      and a.status_id = :codeStatus and b.emplid = :emplId
      


      The SQL works, in that it gives me the most recent record for that employee and type. But I cannot figure out how to write that in HQL.

      Can anyone help me?


        • 1. Re: How to do a Max in a sub query...
          Arnie Morein Newbie

          I've tried this:

          from us.tx.state.oag.olrap.db.InfoApplication as apps
          where apps.codeStatus.statusId = :codeStatus
           and apps.infoWorkflows.emplid = :emplId
           and apps.infoWorkflows.entryDate = (
           select max(entryDate) from apps.infoWorkflows wf
           where wf.infoApplication.applicationId = apps.applicationId
           )
          order by apps.createdDate asc
          


          but I'm getting:



          java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode
          \-[AGGREGATE] AggregateNode: 'max'
          \-[IDENT] IdentNode: 'entryDate' {originalText=entryDate}

          at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
          at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:712)
          at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:536)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4049)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3528)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1690)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
          at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
          at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
          at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
          at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
          at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:77)
          at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:56)
          at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
          at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:400)
          at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:351)
          at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1218)
          at org.jboss.hibernate.jmx.Hibernate.buildSessionFactory(Hibernate.java:231)
          at org.jboss.hibernate.jmx.Hibernate.startService(Hibernate.java:155)
          at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
          at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
          at sun.reflect.GeneratedMethodAccessor79.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
          at $Proxy0.start(Unknown Source)
          at org.jboss.system.ServiceController.start(ServiceController.java:417)
          at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
          at $Proxy4.start(Unknown Source)
          at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
          at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
          at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1015)
          at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
          at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
          at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
          at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
          at $Proxy7.deploy(Unknown Source)
          at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:421)
          at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:634)
          at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
          at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:336)
          at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
          at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
          at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
          at $Proxy0.start(Unknown Source)
          at org.jboss.system.ServiceController.start(ServiceController.java:417)
          at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
          at $Proxy4.start(Unknown Source)
          at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
          at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
          at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
          at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
          at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
          at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
          at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
          at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
          at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
          at $Proxy5.deploy(Unknown Source)
          at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
          at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
          at org.jboss.Main.boot(Main.java:200)
          at org.jboss.Main$1.run(Main.java:490)
          at java.lang.Thread.run(Thread.java:619)



          • 2. Re: How to do a Max in a sub query...
            Arnie Morein Newbie

            This compiles and runs but doesn't get rid of the first record.

            from us.tx.state.oag.olrap.db.InfoApplication as apps
            where
             apps.codeStatus.statusId = :codeStatus
             and apps.infoWorkflows.emplid = :emplId
             and apps.infoWorkflows.entryDate = ( select max(wf.entryDate)
             from apps.infoWorkflows wf
             where wf.infoApplication.applicationId = apps.applicationId
             )
            order by apps.createdDate asc
            


            The SQL generated is a little off...


            13:13:04,031 INFO [STDOUT] Hibernate: select infoapplic0_.application_id as applicat1_494_, infoapplic0_.fiscal_year_id as fiscal2_494_, infoapplic0_.evaluation_id as evaluation3_494_, infoapplic0_.status_id as status4_494_, infoapplic0_.base_award as base5_494_, infoapplic0_.closed_date as closed6_494_, infoapplic0_.created_date as created7_494_, infoapplic0_.emplid as emplid494_, infoapplic0_.hire_date as hire9_494_, infoapplic0_.job_code as job10_494_, infoapplic0_.last_eval_date as last11_494_, infoapplic0_.months_of_service as months12_494_, infoapplic0_.ok_with_bar as ok13_494_, infoapplic0_.submitted_date as submitted14_494_, infoapplic0_.calculated_award as calculated15_494_, infoapplic0_.actual_award as actual16_494_, infoapplic0_.award_date as award17_494_, infoapplic0_.cr_who as cr18_494_, infoapplic0_.cr_when as cr19_494_, infoapplic0_.up_who as up20_494_, infoapplic0_.up_when as up21_494_ from info_application infoapplic0_, info_workflow infoworkfl1_, info_workflow infoworkfl2_ where infoapplic0_.application_id=infoworkfl2_.application_id and infoapplic0_.application_id=infoworkfl1_.application_id and infoapplic0_.status_id=? and infoworkfl1_.emplid=? and infoworkfl2_.entry_date=(select max(infoworkfl3_.entry_date) from info_workflow infoworkfl3_ where infoapplic0_.application_id=infoworkfl3_.application_id and infoworkfl3_.application_id=infoapplic0_.application_id) order by infoapplic0_.created_date asc



            • 3. Re: How to do a Max in a sub query...
              Arnie Morein Newbie

              I figured out a way to do it:

              from us.tx.state.oag.olrap.db.InfoApplication as apps
              where apps.codeStatus.statusId = :codeStatus
               and apps.infoWorkflows.emplid = :emplId
              group by apps.applicationId
               having apps.infoWorkflows.entryDate = max(apps.infoWorkflows.entryDate)
              )
              order by apps.createdDate asc