DB2 query Unknown column name ERRORCODE = -4460, SQLSTATE = null

Question

An employee has baffled us all with a request that fails for reasons that we cannot divine. I can confirm that ENOTE.EN_FILTER_VALUE_L.FILTER_VALUE_DECODE exists in the database. At least three people are out of date, trying to find spelling errors.

Request:

 SELECT sub.ID, sub.USER_ID, sub.EN_TYPE_CODE, typ.EN_TYPE_DESC, typ.APPL_CD, filterval.FILTER_VALUE_DECODE AGENCY_TYPE, sub.EN_TYPE_CODE CONCAT ' | ' CONCAT typ.EN_TYPE_DESC ENOTE_STRING, org1.ORG_LVL1_CD, org1.ORG_LVL1_DC, org2.ORG_LVL2_CD, org2.ORG_LVL2_DC, org3.ORG_LVL3_CD, org3.ORG_LVL3_DC, org4.ORG_LVL4_CD, org4.ORG_LVL4_DC FROM ENOTE.EN_SUBSCRIPTION_T sub LEFT JOIN ENOTE.EN_TYPE_L typ ON sub.EN_TYPE_CODE = typ.EN_TYPE_CODE LEFT JOIN ENOTE.EN_FILTER_OFFICE_T filteroffice ON sub.ID = filteroffice.SUBSCRIPTION_ID LEFT JOIN UMC.ORG_LVL4_L org4 ON org4.ORG_LVL4_CD = filteroffice.ORG_LVL4_CODE AND org4.ORG_LVL3_CD = filteroffice.ORG_LVL3_CODE AND org4.ORG_LVL2_CD = filteroffice.ORG_LVL2_CODE AND org4.ORG_LVL1_CD = filteroffice.ORG_LVL1_CODE AND org4.STRUCTURE_CD = filteroffice.STRUCTURE_CODE LEFT JOIN UMC.ORG_LVL3_L org3 ON org3.ORG_LVL3_CD = filteroffice.ORG_LVL3_CODE AND org3.ORG_LVL2_CD = filteroffice.ORG_LVL2_CODE AND org3.ORG_LVL1_CD = filteroffice.ORG_LVL1_CODE AND org3.STRUCTURE_CD = filteroffice.STRUCTURE_CODE LEFT JOIN UMC.ORG_LVL2_L org2 ON org2.ORG_LVL2_CD = filteroffice.ORG_LVL2_CODE AND org2.ORG_LVL1_CD = filteroffice.ORG_LVL1_CODE AND org2.STRUCTURE_CD = filteroffice.STRUCTURE_CODE LEFT JOIN UMC.ORG_LVL1_L org1 ON org1.ORG_LVL1_CD = filteroffice.ORG_LVL1_CODE AND org1.STRUCTURE_CD = filteroffice.STRUCTURE_CODE LEFT JOIN ENOTE.EN_FILTER_VALUE_L filterval ON (filteroffice.AGENCY_TYPE_CODE = filterval.FILTER_VALUE AND filterval.FILTER_NAME = 'Agency Type') 

An exception:

com.ibm.db2.jcc.a.SqlException: [jcc] [10150] [10300] [4.3.111] Invalid parameter: Unknown column name FILTER_VALUE_DECODE. ERRORCODE = -4460, SQLSTATE = zero

Answer 1

I re-asked this question here db2jcc4.jar Invalid parameter: unknown column name and got an answer.

The combination of Hibernate 3.x and the JCC driver in DB2 9.5 and later has specification level mismatch. Upgrading to Hibernate 4.x will solve the problem, but if you cannot do it in the short term, setting the custom property of the data source useJDBC4ColumnNameAndLabelSemantics=2 will solve the problem.