DB2 query Unknown column name ERRORCODE=-4460, SQLSTATE=null
I re-asked this question here db2jcc4.jar Invalid parameter: Unknown column name and got the answer.
The combination of Hibernate 3.x and the JCC driver in DB2 9.5 and above have a spec level mismatch. Upgrading to Hibernate 4.x will solve the issue, but if you can't do that in the short term setting a custom data source property of useJDBC4ColumnNameAndLabelSemantics=2
solves the issue.
Entropy
Updated on June 14, 2022Comments
-
Entropy almost 2 years
A co-worker has baffled us all with a query that is failing for reasons we can't divine. I can confirm that
ENOTE.EN_FILTER_VALUE_L.FILTER_VALUE_DECODE
does exist in the database. At least three people have stared at it trying to find misspellings.The Query:
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')
The Exception:
com.ibm.db2.jcc.a.SqlException: [jcc][10150][10300][4.3.111] Invalid parameter: Unknown column name FILTER_VALUE_DECODE. ERRORCODE=-4460, SQLSTATE=null
-
Entropy almost 3 yearsPerhaps in your case. For us it was hibernate version (as described in my answer above)