由java.sql.SQLException引起:无效的列索引
由java.sql.SQLException引起:无效的列索引
提问于 2018-09-13 04:25:44
以下是我的代码，我的语句在SQL Developer中工作，但当通过接口作为jca文件中的适配器调用时，它会返回invalid column index错误：
<adapter-config name="SelectCCBAPRecordsWithTemplate" adapter="db" wsdlLocation="../WSDLs/SelectCCBAPRecordsWithTemplate.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">
<connection-factory location="eis/DB/CCBPS-CCBDS" UIConnectionName="CCBPS-CCBDS" adapterRef=""/>
<endpoint-interaction portType="SelectCCBAPRecordsWithTemplate_ptt" operation="SelectCCBAPRecordsWithTemplate">
<interaction-spec className="oracle.tip.adapter.db.DBPureSQLInteractionSpec">
<property name="SqlString" value="SELECT A.AP_REQ_ID,E.GL_ACCT,F.CHAR_VAL,B.SA_ID,B.ADJ_ID,B.CRE_DT,B.ADJ_TYPE_CD,H.CHAR_VAL,FT.GL_DIVISION,G.GL_DIVISION,K.CHAR_VAL,A.ENTITY_NAME,A.COUNTRY,A.ADDRESS1,A.ADDRESS2,A.ADDRESS3,A.ADDRESS4,A.CITY,A.NUM1,A.NUM2,A.COUNTY,A.HOUSE_TYPE,A.STATE,A.POSTAL,B.CURRENCY_CD,A.CURRENCY_PYMNT,A.GEO_CODE,A.IN_CITY_LIMIT,A.PYMNT_METHOD_FLG,B.ADJ_AMT,A.SCHEDULED_PAY_DT,E.FUND_CD, TO_CHAR(NULL) as customRecordElements FROM CI_ADJ_APREQ A, CI_ADJ B,CI_SA C,CI_ADJ_TYPE D,CI_DST_CODE_EFF E,CI_DST_CD_CHAR F,CI_CIS_DIV_CHAR I,CI_CIS_DIV_CHAR K,CI_FT FT,CI_SA_TYPE G,CI_ADJ_TY_CHAR H WHERE A.ADJ_ID = B.ADJ_ID AND B.ADJ_STATUS_FLG ='50' AND A.PYMNT_SEL_STAT_FLG ='N' AND A.BATCH_CD ='APDL' AND A.BATCH_NBR =(select NEXT_BATCH_NBR FROM CI_BATCH_CTRL WHERE BATCH_CD='APDL') AND B.ADJ_ID= FT.SIBLING_ID AND B.SA_ID=C.SA_ID AND B.ADJ_TYPE_CD=D.ADJ_TYPE_CD AND D.DST_ID=E.DST_ID AND E.DST_ID=F.DST_ID AND LTRIM(RTRIM(F.CHAR_TYPE_CD))='TEMPLATE' AND C.CIS_DIVISION = I.CIS_DIVISION AND E.EFFDT=(SELECT MAX (EFFDT) FROM CI_DST_CODE_EFF G WHERE G.DST_ID=E.DST_ID AND G.EFF_STATUS='A' and G.EFFDT<=SYSDATE) AND F.EFFDT=(SELECT MAX (EFFDT) FROM CI_DST_CD_CHAR H WHERE H.DST_ID=F.DST_ID) AND I.EFFDT =(SELECT MAX (EFFDT) FROM CI_CIS_DIV_CHAR J WHERE J.CIS_DIVISION = I.CIS_DIVISION AND J.CHAR_TYPE_CD = I.CHAR_TYPE_CD) AND C.CIS_DIVISION = K.CIS_DIVISION(+) AND (K.EFFDT is null or (K.EFFDT =(SELECT MAX (EFFDT) FROM CI_CIS_DIV_CHAR L WHERE L.CIS_DIVISION = K.CIS_DIVISION AND L.CHAR_TYPE_CD = K.CHAR_TYPE_CD))) AND G.SA_TYPE_CD = C.SA_TYPE_CD AND G.CIS_DIVISION = C.CIS_DIVISION AND H.ADJ_TYPE_CD = D.ADJ_TYPE_CD AND LTRIM(RTRIM(H.CHAR_TYPE_CD)) = 'VENDORID'"/>
</interaction-spec>
</endpoint-interaction>
</adapter-config>例外情况是：
调用绑定时发生
异常。调用JCA绑定时出现异常：“引用操作'SelectCCBAPRecordsWithTemplate‘的JCA绑定执行失败，原因是: Pure SQL Exception.Pure SQL execute of SELECT .......Caused by java.sql.SQLException: Invalid column index
下面是我用来删除重复项的更新后的SQL：
SELECT
A.AP_REQ_ID, E.GL_ACCT, B.SA_ID, B.ADJ_ID, B.CRE_DT,
B.ADJ_TYPE_CD, H.CHAR_VAL, G.GL_DIVISION, A.ENTITY_NAME,
A.COUNTRY, A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.ADDRESS4,
A.CITY, A.NUM1, A.NUM2, A.COUNTY, A.HOUSE_TYPE, A.STATE, A.POSTAL,
B.CURRENCY_CD, A.CURRENCY_PYMNT, A.GEO_CODE, A.IN_CITY_LIMIT,
A.PYMNT_METHOD_FLG, B.ADJ_AMT, A.SCHEDULED_PAY_DT, E.FUND_CD,
TO_CHAR(NULL) AS customRecordElements
FROM
CISADM.CI_ADJ_APREQ A, CISADM.CI_ADJ B, CISADM.CI_SA C,
CISADM.CI_ADJ_TYPE D, CISADM.CI_DST_CODE_EFF E ,CISADM.CI_DST_CD_CHAR F,
CISADM.CI_SA_TYPE G, CISADM.CI_ADJ_TY_CHAR H
WHERE
A.ADJ_ID = B.ADJ_ID
AND B.ADJ_STATUS_FLG = '50'
AND A.PYMNT_SEL_STAT_FLG = 'N'
AND A.BATCH_CD = 'APDL'
AND A.BATCH_NBR = (SELECT NEXT_BATCH_NBR
FROM CISADM.CI_BATCH_CTRL
WHERE BATCH_CD = 'APDL')
AND B.SA_ID = C.SA_ID
AND B.ADJ_TYPE_CD = D.ADJ_TYPE_CD
AND D.DST_ID = E.DST_ID
AND E.DST_ID = F.DST_ID
AND LTRIM(RTRIM(F.CHAR_TYPE_CD)) = 'TEMPLATE'
AND E.EFFDT = (SELECT MAX (EFFDT) FROM CISADM.CI_DST_CODE_EFF I
WHERE I.DST_ID = E.DST_ID
AND I.EFF_STATUS = 'A'
AND I.EFFDT <= SYSDATE)
AND F.EFFDT = (SELECT MAX (EFFDT) FROM CISADM.CI_DST_CD_CHAR J
WHERE J.DST_ID = F.DST_ID)
AND G.SA_TYPE_CD = C.SA_TYPE_CD
AND G.CIS_DIVISION = C.CIS_DIVISION
AND H.ADJ_TYPE_CD = D.ADJ_TYPE_CD
AND LTRIM(RTRIM(H.CHAR_TYPE_CD)) = 'VENDORID'最终编辑:在我的BPEL流程中，有一个我定义的变量，后来我决定不使用它。尽管我删除了向其传递值的所有引用，但我忘记了删除变量本身的声明。我不认为这会导致SQL本身的问题，特别是因为它只是一个声明，但一旦我删除它，代码就开始工作。感谢大家的帮助。
回答 1
Stack Overflow用户
发布于 2018-09-13 04:32:45
我冒失地重新格式化了你的查询，以便更好地查看。它有33列。这意味着您可以使用从1到33的索引来检索值。例如：
rs.getString(1)will work.rs.getString(33)will work.rs.getString(34)将无法工作。超过33列。
您的查询：
SELECT A.AP_REQ_ID, -- 1st column
E.GL_ACCT,
F.CHAR_VAL,
B.SA_ID,
B.ADJ_ID,
B.CRE_DT,
B.ADJ_TYPE_CD,
H.CHAR_VAL,
FT.GL_DIVISION,
G.GL_DIVISION, -- 10th column
K.CHAR_VAL,
A.ENTITY_NAME,
A.COUNTRY,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.ADDRESS4,
A.CITY,
A.NUM1,
A.NUM2, -- 20th column
A.COUNTY,
A.HOUSE_TYPE,
A.STATE,
A.POSTAL,
B.CURRENCY_CD,
A.CURRENCY_PYMNT,
A.GEO_CODE,
A.IN_CITY_LIMIT,
A.PYMNT_METHOD_FLG,
B.ADJ_AMT, -- 30
A.SCHEDULED_PAY_DT,
E.FUND_CD,
TO_CHAR(NULL) as customRecordElements -- 33rd column
FROM CI_ADJ_APREQ A,
CI_ADJ B,
CI_SA C,
CI_ADJ_TYPE D,
CI_DST_CODE_EFF E,
CI_DST_CD_CHAR F,
CI_CIS_DIV_CHAR I,
CI_CIS_DIV_CHAR K,
CI_FT FT,
CI_SA_TYPE G,
CI_ADJ_TY_CHAR H
WHERE A.ADJ_ID = B.ADJ_ID
AND B.ADJ_STATUS_FLG ='50'
AND A.PYMNT_SEL_STAT_FLG ='N'
AND A.BATCH_CD ='APDL'
AND A.BATCH_NBR =
(
select NEXT_BATCH_NBR
FROM CI_BATCH_CTRL
WHERE BATCH_CD='APDL'
)
AND B.ADJ_ID= FT.SIBLING_ID
AND B.SA_ID=C.SA_ID
AND B.ADJ_TYPE_CD=D.ADJ_TYPE_CD
AND D.DST_ID=E.DST_ID
AND E.DST_ID=F.DST_ID
AND LTRIM(RTRIM(F.CHAR_TYPE_CD))='TEMPLATE'
AND C.CIS_DIVISION = I.CIS_DIVISION
AND E.EFFDT=
(
SELECT MAX (EFFDT)
FROM CI_DST_CODE_EFF G
WHERE G.DST_ID=E.DST_ID
AND G.EFF_STATUS='A'
and G.EFFDT<
;
=SYSDATE
)
AND F.EFFDT=(SELECT MAX (EFFDT) FROM CI_DST_CD_CHAR H WHERE H.DST_ID=F.DST_ID)
AND I.EFFDT =
(
SELECT MAX (EFFDT)
FROM CI_CIS_DIV_CHAR J
WHERE J.CIS_DIVISION = I.CIS_DIVISION
AND J.CHAR_TYPE_CD = I.CHAR_TYPE_CD
)
AND C.CIS_DIVISION = K.CIS_DIVISION(+)
AND
(
K.EFFDT is null
or
(
K.EFFDT =
(
SELECT MAX (EFFDT)
FROM CI_CIS_DIV_CHAR L
WHERE L.CIS_DIVISION = K.CIS_DIVISION
AND L.CHAR_TYPE_CD = K.CHAR_TYPE_CD
)
)
)
AND G.SA_TYPE_CD = C.SA_TYPE_CD
AND G.CIS_DIVISION = C.CIS_DIVISION
AND H.ADJ_TYPE_CD = D.ADJ_TYPE_CD
AND LTRIM(RTRIM(H.CHAR_TYPE_CD)) = 'VENDORID'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52302954
复制相关文章
相似问题