sql - 由 java.sql.SQLException 引起:无效的列索引
问题描述
这是我的代码,我的语句在 SQL Developer 中工作,但是当通过接口作为 jca 文件中的适配器调用时,它返回无效的列索引错误:
<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 绑定执行失败,原因是:纯 SQL 异常。SELECT 的纯 SQL 执行 .......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 本身出现问题,特别是因为它只是一个声明,但一旦我删除它,代码就开始工作了。感谢大家的帮助。
解决方案
我冒昧地重新格式化您的查询以更好地查看。它有 33 列。这意味着您可以使用范围从1
到 的索引来检索值33
。例如:
rs.getString(1)
将工作。rs.getString(33)
将工作。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'
推荐阅读
- rest - 如何使用 Rest API 获取 Power BI 报表数据
- android - Github 上的新提交只有几个文件
- swift - 服务应该调用另一个服务还是应该获取自己的数据
- c++ - 使用 arm-none-eabi 进行 Clang 交叉编译
- imagemagick - ImageMagick - 从 Photoshop 文件中删除组
- vue.js - Quasar Framework (Vue):可折叠和鼠标离开
- amazon-web-services - Route 53 + ELB = 不起作用,但似乎配置正确
- docker - 用./configure配置了一个下载的包,如何从centos中彻底删除
- node.js - Microsoft bot 框架 IE11 嵌入问题
- excel - 是否有一个 VBA 字符串来选择曾经有一个空白公式的复制单元格?