[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-2623) DB2 Alias name too long

DB2 Alias name too long ------------------------ Key: HHH-2623 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2623 Project: Hibernate3 Issue Type: Bug Affects Versions: 3.0 final Environment: DB2 v. 7, OS390. Reporter: Eric Scott Priority: Critical I am getting DB2 SQL error: SQLCODE: -107 (name too long) exceptions when trying to select rows based on Many-to-one or one-to-many relationships. Looking at the generated sql I can see that the Alias name generated for the TimeStamp column is one character too long for DB2's max of 18. The alias when not searching using a many-to-one or one-to-many relationship is SUBMISSION28_48_9_ but for the same property the alias changes to SUBMISSION28_48_10_ which is 19 chars long, one too many for DB2. Is this a bug in Hibernate's DB2390 dialect? I've seen similar problems in the forums and the only solution is to extend the dialect or Column class. Is there any other way to get rid of this problem? [b]Hibernate version:[/b] 3.0 [b]Mapping documents:[/b] <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" > <hibernate-mapping> <class name="ca.gc.rcmp.rtid.npsnistserver.dao.submission.UISubmission" table="SUBMISSION" > <id name="submissionId" column="SUBMISSION_ID" type="java.lang.Long"> <generator class="identity"/> </id> <property name="civilApplicationType" column="CIVIL_APP_TP" type="java.lang.Integer" /> <property name="submissionTypeId" column="TOT_ID" type="java.lang.Integer" /> <property name="originatingAgencyId" column="ORIG_AGCY_ORI" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType7" /> <property name="dcn" column="DCN" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType20" /> <property name="paperConversionNote" column="PAPER_CONV_NT" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType255" /> <property name="paperConversionIndicator" column="PAPER_CONV_IND" type="java.lang.Boolean" /> <property name="remandIndicator" column="REMAND_IND" type="java.lang.Boolean" not-null="true" /> <property name="dispositionIndicator" column="DISPOSITION_IND" type="java.lang.Boolean" not-null="true" /> <property name="sensitiveIndicator" column="SENSITIVE_IND" type="java.lang.Boolean" not-null="true" /> <property name="foreignIndicator" column="FOREIGN_IND" type="java.lang.Boolean" not-null="true" /> <property name="wipIndicator" column="WIP_IND" type="java.lang.Boolean" not-null="true" /> <property name="thirdPartyAuthObtainedIndicator" column="TPTY_WAIVER_IND" type="java.lang.Boolean" not-null="true" /> <property name="retentionDate" column="RETENTION_DT" type="java.util.Date" /> <property name="biodataPurgeDate" column="BIODATA_PURGE_DT" type="java.util.Date" /> <property name="caseFileNumber" column="CASE_FILE_NO" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType50" /> <property name="responsiblePersonName" column="RESPBL_PRSN_NM" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType50" /> <property name="slaId" column="SLA_ID" type="java.lang.Integer" /> <property name="stateId" column="STATE_ID" type="java.lang.Integer" /> <property name="foreignSearchReason" column="FOREIGN_SEARCH_RSN" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType500" /> <property name="externalTCN" column="EXT_TCN" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType40" /> <property name="civilApplicationTypeSpecify" column="CIVIL_APP_TP_SPEC" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType50" /> <property name="agencyReferenceNumber" column="AGCY_REF_NO" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType32" /> <property name="priority" column="PRIORITY" type="java.lang.Integer" /> <property name="employmentInVulnerableSectorIndicator" column="EMP_VULNERABLE_IND" type="java.lang.Boolean" /> <property name="endTimestamp" column="END_TMSTMP" type="java.util.Date" /> <property name="submissionTimestamp" column="SUBMISSION_TMSTMP" type="java.util.Date" /> <property name="sendResults" column="SEND_RESULTS" type="java.lang.Integer" /> <property name="language" column="LANGUAGE" type="java.lang.Integer" /> <property name="processID" column="PROCESS_ID" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType60" /> <property name="outputScenarioID" column="OUTPUT_SCENARIO_ID" type="java.lang.Integer" /> <property name="dnaIndicator" column="DNA_IND" type="java.lang.Boolean" not-null="true" /> <property name="fbiStateId" column="FBI_STATE_ID" type="java.lang.Integer" /> <property name="palmReverseSearchStateId" column="PLM_RSRH_STATE_ID" type="java.lang.Integer" /> <property name="fingerReverseSearchStateId" column="FING_RSRH_STATE_ID" type="java.lang.Integer" /> <property name="releaseRuleType" column="RELEASE_RULE_TP" type="java.lang.Integer" /> <property name="latentFileId" column="LATENT_FILE_ID" type="java.lang.Integer" /> <property name="resubmittedIndicator" column="RESUBMITTED_IND" type="java.lang.Boolean" not-null="true" /> <property name="captureNodeLocation" column="CAPTURE_NODE_LOC" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType32" /> <property name="operatorName" column="OPERATOR_NM" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType50" /> <property name="forwardToOverride" column="FORWARD_TO_OVRD" type="ca.gc.rcmp.rtid.npsnistserver.util.TruncateStringType$TruncateStringType80" /> <many-to-one name="crimsTeam" column="CRIMS_TEAM_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.icd.CommonCodeValue" insert="false" update="false" /> <many-to-one name="sla" column="SLA_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.SLA" lazy="false" insert="false" update="false" /> <many-to-one name="submissionType" column="TOT_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.icd.ICDSubmissionType" insert="false" update="false" /> <many-to-one name="mainState" column="STATE_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.State" insert="false" update="false" /> <many-to-one name="fbiState" column="FBI_STATE_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.State" insert="false" update="false" /> <many-to-one name="palmReverseSearchState" column="PLM_RSRH_STATE_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.State" insert="false" update="false" /> <many-to-one name="fingerReverseSearchState" column="FING_RSRH_STATE_ID" class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.State" insert="false" update="false" /> <many-to-one name="originatingAgency" column="ORIG_AGCY_ORI" class="ca.gc.rcmp.rtid.npsnistserver.dao.agency.Agency" insert="false" update="false" /> <bag name="biodataList" table="BIODATA" inverse="true" lazy="true" cascade="delete"> <key column="SUBMISSION_ID" not-null="true"/> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.Biodata" /> </bag> <bag name="transactionList" table="TRANSACTION" inverse="true" lazy="true" cascade="all"> <key column="SUBMISSION_ID" not-null="true"/> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.Transaction" /> </bag> <bag name="submissionFileNumbers" table="SUB_FILE_NUM" inverse="true" lazy="true" cascade="all"> <key column="SUBMISSION_ID" not-null="true"/> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.SubmissionFileNumber" /> </bag> <bag name="activityLogs" table="ACTIVITY_LOG" inverse="true" lazy="true" cascade="all"> <key column="SUBMISSION_ID" not-null="true"/> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.ActivityLog" /> </bag> <bag name="compressionRatioLogList" table="CMPRS_RTIO_LOG" inverse="true" lazy="true" cascade="all"> <key column="SUBMISSION_ID" not-null="true"/> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.CompressionRatioLog" /> </bag> <bag name="masterDocumentReferenceNumbers" table="MASTER_DOC_REF" inverse="true" lazy="true" cascade="none"> <key column="SUBMISSION_ID" /> <one-to-many class="ca.gc.rcmp.rtid.npsnistserver.dao.submission.MasterDocumentReference" /> </bag> </class> </hibernate-mapping> [b]Full stack trace of any exception that occurs:[/b] ca.gc.rcmp.rtid.npsnistserver.dao.NISTServerDAOException: org.hibernate.exception.SQLGrammarException: could not execute query at ca.gc.rcmp.rtid.npsnistserver.dao.SubmissionConsoleDAO.advancedSubmissionSearch(SubmissionConsoleDAO.java:648 ) at SubmissionConsole.SubmissionConsoleController.doAdvancedSubmissionSearch(SubmissionConsoleController.jpf:2419 ) 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:324) at com.bea.wlw.netui.pageflow.FlowController.invokeActionMethod(FlowController.java:1510) at com.bea.wlw.netui.pageflow.FlowController.getActionMethodForward(FlowController.java:1445) at com.bea.wlw.netui.pageflow.FlowController.internalExecute(FlowController.java:776) at com.bea.wlw.netui.pageflow.PageFlowController.internalExecute(PageFlowController.java:211) at com.bea.wlw.netui.pageflow.FlowController.execute(FlowController.java:606) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.processActionPerform(PageFlowRequestProcessor.java:1354) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.process(PageFlowRequestProcessor.java:650) at com.bea.wlw.netui.pageflow.AutoRegisterActionServlet.process(AutoRegisterActionServlet.java:527) at com.bea.wlw.netui.pageflow.PageFlowActionServlet.process(PageFlowActionServlet.java:152) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1072) at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465) at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:28) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at ca.gc.rcmp.rtid.npsnistserver.ui.filter.HibernateSessionRequestFilter.doFilter(HibernateSessionRequestFilter. java:56) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at ca.gc.rcmp.rtid.npsnistserver.ui.filter.CredentialsFilter.doFilter(CredentialsFilter.java:112) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at org.extremecomponents.table.filter.AbstractExportFilter.doFilter(AbstractExportFilter.java:49) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:326) at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.superForward(PageFlowRequestProcessor.java:1301) at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor$DefaultHttpRedirector.forward(PageFlowRequestProcessor.ja va:1317) at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.process(PageFlowRequestProcessor.java:554) at com.bea.wlw.netui.pageflow.AutoRegisterActionServlet.process(AutoRegisterActionServlet.java:527) at com.bea.wlw.netui.pageflow.PageFlowActionServlet.process(PageFlowActionServlet.java:152) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1072) at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465) at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:28) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at ca.gc.rcmp.rtid.npsnistserver.ui.filter.HibernateSessionRequestFilter.doFilter(HibernateSessionRequestFilter. java:56) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at ca.gc.rcmp.rtid.npsnistserver.ui.filter.CredentialsFilter.doFilter(CredentialsFilter.java:112) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at org.extremecomponents.table.filter.AbstractExportFilter.doFilter(AbstractExportFilter.java:49) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27) at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6987) at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121) at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3892) at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2766) at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224) at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1570) at org.hibernate.loader.Loader.list(Loader.java:1550) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1252) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299) at ca.gc.rcmp.rtid.npsnistserver.dao.SubmissionConsoleDAO.advancedSubmissionSearch(SubmissionConsoleDAO.java:645 ) ... 55 more Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -107, SQLSTATE: 42622, SQLERRMC: SUBMISSION28_48_10_; 18 at com.ibm.db2.jcc.b.zc.e(zc.java:1606) at com.ibm.db2.jcc.b.zc.a(zc.java:1206) at com.ibm.db2.jcc.b.zc.a(zc.java:1192) at com.ibm.db2.jcc.a.db.h(db.java:142) at com.ibm.db2.jcc.a.db.a(db.java:43) at com.ibm.db2.jcc.a.r.a(r.java:30) at com.ibm.db2.jcc.a.sb.g(sb.java:152) at com.ibm.db2.jcc.b.zc.n(zc.java:1186) at com.ibm.db2.jcc.b.ad.db(ad.java:1763) at com.ibm.db2.jcc.b.ad.d(ad.java:2205) at com.ibm.db2.jcc.b.ad.U(ad.java:489) at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472) at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:124) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:117) at org.hibernate.loader.Loader.getResultSet(Loader.java:1249) at org.hibernate.loader.Loader.doQuery(Loader.java:369) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:209) at org.hibernate.loader.Loader.doList(Loader.java:1567) ... 60 more [b]Name and version of the database you are using:[/b] DB2 v. 7 using DB2390Dialect. [b]The generated SQL (show_sql=true):[/b] Hibernate: select this_.SUBMISSION_ID as SUBMISSION1_10_, this_.CIVIL_APP_TP as CIVIL2_48_10_, this_.TOT_ID as TOT3_48_1 0_, this_.ORIG_AGCY_ORI as ORIG4_48_10_, this_.DCN as DCN48_10_, this_.PAPER_CONV_NT as PAPER6_48_10_, this_.PAPER_CONV_ IND as PAPER7_48_10_, this_.REMAND_IND as REMAND8_48_10_, this_.DISPOSITION_IND as DISPOSIT9_48_10_, this_.SENSITIVE_IND as SENSITIVE10_48_10_, this_.FOREIGN_IND as FOREIGN11_48_10_, this_.WIP_IND as WIP12_48_10_, this_.TPTY_WAIVER_IND as T PTY13_48_10_, this_.RETENTION_DT as RETENTION14_48_10_, this_.BIODATA_PURGE_DT as BIODATA15_48_10_, this_.CASE_FILE_NO a s CASE17_48_10_, this_.RESPBL_PRSN_NM as RESPBL18_48_10_, this_.SLA_ID as SLA19_48_10_, this_.STATE_ID as STATE20_48_10_ , this_.FOREIGN_SEARCH_RSN as FOREIGN21_48_10_, this_.EXT_TCN as EXT22_48_10_, this_.CIVIL_APP_TP_SPEC as CIVIL23_48_10_ , this_.AGCY_REF_NO as AGCY24_48_10_, this_.PRIORITY as PRIORITY48_10_, this_.EMP_VULNERABLE_IND as EMP26_48_10_, this_. END_TMSTMP as END27_48_10_, this_.SUBMISSION_TMSTMP as SUBMISSION28_48_10_, this_.SEND_RESULTS as SEND29_48_10_, this_.L ANGUAGE as LANGUAGE48_10_, this_.PROCESS_ID as PROCESS31_48_10_, this_.OUTPUT_SCENARIO_ID as OUTPUT32_48_10_, this_.DNA_ IND as DNA33_48_10_, this_.FBI_STATE_ID as FBI34_48_10_, this_.PLM_RSRH_STATE_ID as PLM35_48_10_, this_.FING_RSRH_STATE_ ID as FING36_48_10_, this_.RELEASE_RULE_TP as RELEASE37_48_10_, this_.LATENT_FILE_ID as LATENT38_48_10_, this_.RESUBMITT ED_IND as RESUBMI39_48_10_, this_.CAPTURE_NODE_LOC as CAPTURE40_48_10_, this_.OPERATOR_NM as OPERATOR41_48_10_, this_.FO RWARD_TO_OVRD as FORWARD42_48_10_, this_.CRIMS_TEAM_ID as CRIMS43_48_10_, commoncode4_.COMMON_CD_VAL_ID as COMMON1_0_, c ommoncode4_.COMMON_CD_TBL_ID as COMMON2_36_0_, commoncode4_.CODE as CODE36_0_, commoncode4_.NAME_EN as NAME4_36_0_, comm oncode4_.NAME_FR as NAME5_36_0_, icdsubmiss5_.TOT_ID as TOT1_1_, icdsubmiss5_.TOT_ACRONYM as TOT2_2_1_, icdsubmiss5_.NAM E_EN as NAME3_2_1_, icdsubmiss5_.NAME_FR as NAME4_2_1_, icdsubmiss5_.SERVICE_NAME_EN as SERVICE5_2_1_, icdsubmiss5_.SERV ICE_NAME_FR as SERVICE6_2_1_, icdsubmiss5_.RETAIN_FL as RETAIN7_2_1_, icdsubmiss5_.INCOMING_TX as INCOMING8_2_1_, icdsub miss5_.SUBMISSION_CAT_ID as SUBMISSION9_2_1_, icdsubmiss6_.SUBMISSION_CAT_ID as SUBMISSION1_2_, icdsubmiss6_.NAME_EN as NAME2_3_2_, icdsubmiss6_.NAME_FR as NAME3_3_2_, state1_.STATE_ID as STATE1_3_, state1_.NAME_EN as NAME2_63_3_, state1_.N AME_FR as NAME3_63_3_, state1_.INSERVICE as INSERVICE63_3_, agency8_.RTID_ORI as RTID1_4_, agency8_.XREF_ORI as XREF2_44 _4_, agency8_.FORMAL_NAME as FORMAL3_44_4_, agency8_.CMN_NAME_EN as CMN4_44_4_, agency8_.CMN_NAME_FR as CMN5_44_4_, agen cy8_.SECTION_NAME as SECTION6_44_4_, agency8_.HOST_CPIC_ORI as HOST7_44_4_, agency8_.RTID_TRANS_ORI as RTID8_44_4_, agen cy8_.CPIC_AGCY_ST as CPIC9_44_4_, agency8_.NON_CDN_IND as NON10_44_4_, agency8_.CVL_BULK_BILL_IND as CVL11_44_4_, agency 8_.CPIC_USR_AGCY_IND as CPIC12_44_4_, agency8_.NON_CNV_ELG_IND as NON13_44_4_, agency8_.YOUNG_PRSN_ELG_IND as YOUNG14_44 _4_, agency8_.VUL_SCTR_ELIG_IND as VUL15_44_4_, agency8_.AGENCY_GROUP_ID as AGENCY16_44_4_, agency8_.AGENCY_TP_ID as AGE NCY17_44_4_, agency8_.PREFERRED_LANG as PREFERRED18_44_4_, agency8_.AUTO_RELEASE_LVL as AUTO19_44_4_, agency8_.CONTACT_T ITLE as CONTACT20_44_4_, agency8_.PHY_CIVIC_ADDR as PHY21_44_4_, agency8_.PHY_MUNIC as PHY22_44_4_, agency8_.PHY_POSTAL_ CD as PHY23_44_4_, agency8_.PHY_PROV_ST_CD as PHY24_44_4_, agency8_.PHY_CNTRY_ISO_CD as PHY25_44_4_, agency8_.PHY_PHONE_ NO as PHY26_44_4_, agency8_.PHY_PHONE_EXTN as PHY27_44_4_, agency8_.PHY_FAX_NO as PHY28_44_4_, agency8_.OPERATIONAL_EMAI L as OPERATI29_44_4_, agency8_.SYSTEM_TP_ID as SYSTEM30_44_4_, agency8_.RIN as RIN44_4_, agency8_.MLG_ATTENTION_NM as ML G32_44_4_, agency8_.MLG_CIVIC_ADDR as MLG33_44_4_, agency8_.MLG_MUNIC as MLG34_44_4_, agency8_.MLG_POSTAL_CD as MLG35_44 _4_, agency8_.MLG_CNTRY_SUBDV_CD as MLG36_44_4_, agency8_.MLG_CNTRY_ISO_CD as MLG37_44_4_, agency8_.DEVICE_INTR_TP_ID as DEVICE38_44_4_, agency8_.TRANS_ROUTING_LOC as TRANS39_44_4_, agency8_.FBI_ORDER_IND as FBI40_44_4_, agency8_.CREATED_DT as CREATED41_44_4_, agency8_.LAST_MODIFIED_DT as LAST42_44_4_, commoncode9_.COMMON_CD_VAL_ID as COMMON1_5_, commoncode9 _.COMMON_CD_TBL_ID as COMMON2_36_5_, commoncode9_.CODE as CODE36_5_, commoncode9_.NAME_EN as NAME4_36_5_, commoncode9_.N AME_FR as NAME5_36_5_, commoncode10_.COMMON_CD_VAL_ID as COMMON1_6_, commoncode10_.COMMON_CD_TBL_ID as COMMON2_36_6_, co mmoncode10_.CODE as CODE36_6_, commoncode10_.NAME_EN as NAME4_36_6_, commoncode10_.NAME_FR as NAME5_36_6_, commoncode11_ .COMMON_CD_VAL_ID as COMMON1_7_, commoncode11_.COMMON_CD_TBL_ID as COMMON2_36_7_, commoncode11_.CODE as CODE36_7_, commo ncode11_.NAME_EN as NAME4_36_7_, commoncode11_.NAME_FR as NAME5_36_7_, commoncode12_.COMMON_CD_VAL_ID as COMMON1_8_, com moncode12_.COMMON_CD_TBL_ID as COMMON2_36_8_, commoncode12_.CODE as CODE36_8_, commoncode12_.NAME_EN as NAME4_36_8_, com moncode12_.NAME_FR as NAME5_36_8_, submission2_.SUB_FILE_NUM_ID as SUB1_9_, submission2_.SUBMISSION_ID as SUBMISSION2_55 _9_, submission2_.FILE_TP_ID as FILE3_55_9_, submission2_.FILE_NO as FILE4_55_9_, submission2_.SUBMITTED_IND as SUBMITTE D5_55_9_, submission2_.ASSIGNED_IND as ASSIGNED6_55_9_, submission2_.CERTIFIED_IND as CERTIFIED7_55_9_, submission2_.HIT _IND as HIT8_55_9_, submission2_.FAIL_CERT_IND as FAIL9_55_9_ from NPSNIST.SUBMISSION this_ left outer join NPSNIST.COMM ON_CODE_VALUE commoncode4_ on this_.CRIMS_TEAM_ID=commoncode4_.COMMON_CD_VAL_ID left outer join NPSNIST.SUBMISSION_TYPE icdsubmiss5_ on this_.TOT_ID=icdsubmiss5_.TOT_ID left outer join NPSNIST.SUBMISSION_CAT icdsubmiss6_ on icdsubmiss5_.SUB MISSION_CAT_ID=icdsubmiss6_.SUBMISSION_CAT_ID inner join NPSNIST.STATE state1_ on this_.STATE_ID=state1_.STATE_ID left o uter join NPSNIST.AGENCY agency8_ on this_.ORIG_AGCY_ORI=agency8_.RTID_ORI left outer join NPSNIST.COMMON_CODE_VALUE com moncode9_ on agency8_.AGENCY_GROUP_ID=commoncode9_.COMMON_CD_VAL_ID left outer join NPSNIST.COMMON_CODE_VALUE commoncode 10_ on agency8_.AGENCY_TP_ID=commoncode10_.COMMON_CD_VAL_ID left outer join NPSNIST.COMMON_CODE_VALUE commoncode11_ on a gency8_.DEVICE_INTR_TP_ID=commoncode11_.COMMON_CD_VAL_ID left outer join NPSNIST.COMMON_CODE_VALUE commoncode12_ on agen cy8_.SYSTEM_TP_ID=commoncode12_.COMMON_CD_VAL_ID inner join NPSNIST.SUB_FILE_NUM submission2_ on this_.SUBMISSION_ID=sub mission2_.SUBMISSION_ID where (this_.TOT_ID=? or this_.TOT_ID=? or this_.TOT_ID=? or this_.TOT_ID=? or this_.TOT_ID=? or this_.TOT_ID=? or this_.TOT_ID=?) and (state1_.INSERVICE=?) and submission2_.FILE_TP_ID=? and submission2_.FILE_NO like ?