DB2查询未知列名称ERRORCODE = -4460，SQLSTATE = null [英] DB2 query Unknown column name ERRORCODE=-4460, SQLSTATE=null

问题描述

一个同事对我们所有人的疑惑感到困惑，因为我们不能神圣的原因而失败了。我可以确认数据库中存在 ENOTE.EN_FILTER_VALUE_L.FILTER_VALUE_DECODE 。至少有三个人盯着它试图找到拼写错误。

查询：

 code> 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 = filtero ffice.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 ='代理商类型'）
  

异常：

com.ibm.db2.jcc.a.SqlException：[jcc] [10150] [10300] [4.3.111]
参数无效：未知列名FILTER_VALUE_DECODE。 ERRORCODE = -4460，
SQLSTATE = null

解决方案

我重新询问这里的问题 db2jcc4.jar无效参数：未知列名称，并得到回答。

DB2 9.5及更高版本中的Hibernate 3.x和JCC驱动程序的组合具有规格级别不匹配。升级到Hibernate 4.x将会解决问题，但是如果您在短期内无法执行此操作，可以使用useJDBC4ColumnNameAndLabelSemantics = 2的自定义数据源属性来解决问题。

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

解决方案

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.

这篇关于DB2查询未知列名称ERRORCODE = -4460，SQLSTATE = null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！