* 질문 등록 시 다음의 내용을 꼭 기입하여 주세요.
|
|
Linux |
|
|
9.3 (9.3.6. 0002) |
|
|
[도움말]-[버전정보] 확인 |
|
|
Java |
* CUBRID 응용 오류, SQL 오류 또는 SQL 튜닝 관련된 문의는 반드시 다음의 내용을 추가해 주세요. 비밀글이나 비밀 댓글도 가능합니다.
* 저희가 상황을 이해하고, 재현이 가능해야 알 수 있는 문제들이 많습니다. 가능한 정보/정황들을 부탁합니다.
| 에러 내용 및 재현 방법 | 재현 가능한 Source와 SQL |
| 관련 테이블(인덱스, 키정보 포함) 정보 | CUBRID 홈 디렉토리 아래 log 디렉토리 압축 |
-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
안녕하세요.
Merge 쿼리 실행 중 발생한 오류 Cubrid Data Type Error 에러 관련해서 도움을 구하고자 문의 드립니다.
현재 데이터를 List로 받아서 Merge 쿼리를 통해 UPDATE 또는 INSERT를 진행하는 쿼리를 실행하려고 했습니다만 다음과 같은 오류가 발생했습니다.
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [-456];
--- The error occurred while applying a parameter map.
--- Check the EhojoToCubridServiceDAO.updateTcmCtrtbooks-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: cubrid.jdbc.driver.CUBRIDException: Data type references are incompatible.
쿼리를 실행하는 도중 데이터타입이 맞지 않다는 오류인 것 같아 다시 살펴보았으나, 제가 보기에는 문제가 없는것으로 생각됩니다.
실행한 쿼리는 다음과 같으며, 테이블의 DDL, 사용한 VO를 첨부하였습니다. 혹시 제가 쿼리를 잘못 사용한게 있을까요?
<update id="MethodName" parameterClass="java.util.List">
MERGE INTO V_TCM_CTRTBOOKS AS A
USING (
<iterate prepend="VALUES" conjunction=",">
(
#[].lafCd# AS "LAF_CD",
#[].ctrtLdgrMngNo# AS "CTRT_LDGR_MNG_NO",
#[].atflGrpId# AS "ATFL_GRP_ID",
#[].fyr# AS "FYR",
#[].expsDvCd# AS "EXPS_DV_CD",
#[].pcurDvCd# AS "PCUR_DV_CD",
#[].ctrtKndCd# AS "CTRT_KND_CD",
#[].ctrtMthCd# AS "CTRT_MTH_CD",
#[].ctrtTyDtsCd# AS "CTRT_TY_DTS_CD",
#[].newLtmDvCd# AS "NEW_LTM_DV_CD",
#[].cprtCtrtDvCd# AS "CPRT_CTRT_DV_CD",
#[].ctrtTrgtNm# AS "CTRT_TRGT_NM",
#[].ctrtOtlnCn# AS "CTRT_OTLN_CN",
#[].lctnNm# AS "LCTN_NM",
#[].ctrtNo# AS "CTRT_NO",
#[].pcurMngNo# AS "PCUR_MNG_NO",
#[].pcurChrgDeptNm# AS "PCUR_CHRG_DEPT_NM",
#[].cgpTelno# AS "CGP_TELNO",
CAST(#[].pcurFee# AS NUMERIC(20,0)) AS "PCUR_FEE",
#[].podrDvCd# AS "PODR_DV_CD",
CAST(#[].frstSmzCtrtAmt# AS NUMERIC(20,0)) AS "FRST_SMZ_CTRT_AMT",
CAST(#[].smzCtrtTottAmt# AS NUMERIC(20,0)) AS "SMZ_CTRT_TOTT_AMT",
#[].smzCtrtYmd# AS "SMZ_CTRT_YMD",
#[].bgcsYmd# AS "BGCS_YMD",
#[].cmcnYmd# AS "CMCN_YMD",
#[].cmcnScdYmd# AS "CMCN_SCD_YMD",
#[].rgetYn# AS "RGET_YN",
CAST(#[].dbtBdnAmt# AS NUMERIC(20,0)) AS "DBT_BDN_AMT",
#[].smzCstcDys# AS "SMZ_CSTC_DYS",
#[].pvcnRsonCd# AS "PVCN_RSON_CD",
#[].pvcnRsonSclsCd# AS "PVCN_RSON_SCLS_CD",
#[].g2bRchDt# AS "G2B_RCH_DT",
#[].ctrtFomCd# AS "CTRT_FOM_CD",
#[].mdfcnYmd# AS "MDFCN_YMD",
#[].ctrtChrgUsrId# AS "CTRT_CHRG_USR_ID",
CAST(#[].bfSmzCtrtAmt# AS NUMERIC(20,0)) AS "BF_SMZ_CTRT_AMT",
CAST(#[].dlyRpnAmtRt# AS NUMERIC(25,5)) AS "DLY_RPN_AMT_RT",
#[].frstCtrtLdgrNo# AS "FRST_CTRT_LDGR_NO",
#[].deptCd# AS "DEPT_CD",
#[].pcurPrgDvCd# AS "PCUR_PRG_DV_CD",
#[].exntCd# AS "EXNT_CD",
#[].mdfcnDt# AS "MDFCN_DT",
#[].mdfcnUsrId# AS "MDFCN_USR_ID",
#[].giveMthCd# AS "GIVE_MTH_CD",
#[].leasYn# AS "LEAS_YN",
#[].smzCtrtDgr# AS "SMZ_CTRT_DGR",
#[].smzCtrtDgrExiYn# AS "SMZ_CTRT_DGR_EXI_YN",
#[].ctrtOpslDvCd# AS "CTRT_OPSL_DV_CD",
#[].ctrtMthDtsDvCd# AS "CTRT_MTH_DTS_DV_CD",
#[].ctdmDvCd# AS "CTDM_DV_CD",
#[].ctrtCnltYn# AS "CTRT_CNLT_YN",
#[].ctrtCn# AS "CTRT_CN",
CAST(#[].pbnTottAmt# AS NUMERIC(20,0)) AS "PBN_TOTT_AMT",
CAST(#[].rnsmTottAmt# AS NUMERIC(20,0)) AS "RNSM_TOTT_AMT",
#[].cmpsMbizCd# AS "CMPS_MBIZ_CD",
CAST(#[].lbcTottAmt# AS NUMERIC(20,0)) AS "LBC_TOTT_AMT",
#[].scgdYn# AS "SCGD_YN",
#[].g2bCkNm# AS "G2B_CK_NM",
CAST(#[].g2bGdPatDscrt# AS NUMERIC(22,2)) AS "G2B_GD_PAT_DSCRT",
CAST(#[].g2bGdPatDscAmt# AS NUMERIC(20,0)) AS "G2B_GD_PAT_DSC_AMT",
#[].gvslMtrlDvCd# AS "GVSL_MTRL_DV_CD",
#[].cstcDvCd# AS "CSTC_DV_CD",
#[].lgnRgstrUsrId# AS "LGN_RGSTR_USR_ID",
#[].lgnRgstrDt# AS "LGN_RGSTR_DT",
#[].frstRgstrUsrId# AS "FRST_RGSTR_USR_ID",
#[].frstRgstrDt# AS "FRST_RGSTR_DT",
#[].lastMdfcnUsrId# AS "LAST_MDFCN_USR_ID",
#[].lastMdfcnDt# AS "LAST_MDFCN_DT"
)
</iterate>
) AS B
ON (
B.LAF_CD = A.LAF_CD
AND B.CTRT_LDGR_MNG_NO = A.CTRT_LDGR_MNG_NO
)
WHEN MATCHED THEN
UPDATE SET A.ATFL_GRP_ID = B.ATFL_GRP_ID
, A.FYR = B.FYR
, A.EXPS_DV_CD = B.EXPS_DV_CD
, A.PCUR_DV_CD = B.PCUR_DV_CD
, A.CTRT_KND_CD = B.CTRT_KND_CD
, A.CTRT_MTH_CD = B.CTRT_MTH_CD
, A.CTRT_TY_DTS_CD = B.CTRT_TY_DTS_CD
, A.NEW_LTM_DV_CD = B.NEW_LTM_DV_CD
, A.CPRT_CTRT_DV_CD = B.CPRT_CTRT_DV_CD
, A.CTRT_TRGT_NM = B.CTRT_TRGT_NM
, A.CTRT_OTLN_CN = B.CTRT_OTLN_CN
, A.LCTN_NM = B.LCTN_NM
, A.CTRT_NO = B.CTRT_NO
, A.PCUR_MNG_NO = B.PCUR_MNG_NO
, A.PCUR_CHRG_DEPT_NM = B.PCUR_CHRG_DEPT_NM
, A.CGP_TELNO = B.CGP_TELNO
, A.PCUR_FEE = B.PCUR_FEE
, A.PODR_DV_CD = B.PODR_DV_CD
, A.FRST_SMZ_CTRT_AMT = B.FRST_SMZ_CTRT_AMT
, A.SMZ_CTRT_TOTT_AMT = B.SMZ_CTRT_TOTT_AMT
, A.SMZ_CTRT_YMD = B.SMZ_CTRT_YMD
, A.BGCS_YMD = B.BGCS_YMD
, A.CMCN_YMD = B.CMCN_YMD
, A.CMCN_SCD_YMD = B.CMCN_SCD_YMD
, A.RGET_YN = B.RGET_YN
, A.DBT_BDN_AMT = B.DBT_BDN_AMT
, A.SMZ_CSTC_DYS = B.SMZ_CSTC_DYS
, A.PVCN_RSON_CD = B.PVCN_RSON_CD
, A.PVCN_RSON_SCLS_CD = B.PVCN_RSON_SCLS_CD
, A.G2B_RCH_DT = B.G2B_RCH_DT
, A.CTRT_FOM_CD = B.CTRT_FOM_CD
, A.MDFCN_YMD = B.MDFCN_YMD
, A.CTRT_CHRG_USR_ID = B.CTRT_CHRG_USR_ID
, A.BF_SMZ_CTRT_AMT = B.BF_SMZ_CTRT_AMT
, A.DLY_RPN_AMT_RT = B.DLY_RPN_AMT_RT
, A.FRST_CTRT_LDGR_NO = B.FRST_CTRT_LDGR_NO
, A.DEPT_CD = B.DEPT_CD
, A.PCUR_PRG_DV_CD = B.PCUR_PRG_DV_CD
, A.EXNT_CD = B.EXNT_CD
, A.MDFCN_DT = B.MDFCN_DT
, A.MDFCN_USR_ID = B.MDFCN_USR_ID
, A.GIVE_MTH_CD = B.GIVE_MTH_CD
, A.LEAS_YN = B.LEAS_YN
, A.SMZ_CTRT_DGR = B.SMZ_CTRT_DGR
, A.SMZ_CTRT_DGR_EXI_YN = B.SMZ_CTRT_DGR_EXI_YN
, A.CTRT_OPSL_DV_CD = B.CTRT_OPSL_DV_CD
, A.CTRT_MTH_DTS_DV_CD = B.CTRT_MTH_DTS_DV_CD
, A.CTDM_DV_CD = B.CTDM_DV_CD
, A.CTRT_CNLT_YN = B.CTRT_CNLT_YN
, A.CTRT_CN = B.CTRT_CN
, A.PBN_TOTT_AMT = B.PBN_TOTT_AMT
, A.RNSM_TOTT_AMT = B.RNSM_TOTT_AMT
, A.CMPS_MBIZ_CD = B.CMPS_MBIZ_CD
, A.LBC_TOTT_AMT = B.LBC_TOTT_AMT
, A.SCGD_YN = B.SCGD_YN
, A.G2B_CK_NM = B.G2B_CK_NM
, A.G2B_GD_PAT_DSCRT = B.G2B_GD_PAT_DSCRT
, A.G2B_GD_PAT_DSC_AMT = B.G2B_GD_PAT_DSC_AMT
, A.GVSL_MTRL_DV_CD = B.GVSL_MTRL_DV_CD
, A.CSTC_DV_CD = B.CSTC_DV_CD
, A.LGN_RGSTR_USR_ID = B.LGN_RGSTR_USR_ID
, A.LGN_RGSTR_DT = B.LGN_RGSTR_DT
, A.FRST_RGSTR_USR_ID = B.FRST_RGSTR_USR_ID
, A.FRST_RGSTR_DT = B.FRST_RGSTR_DT
, A.LAST_MDFCN_USR_ID = B.LAST_MDFCN_USR_ID
, A.LAST_MDFCN_DT = B.LAST_MDFCN_DT
WHEN NOT MATCHED THEN
INSERT VALUES
(
B.LAF_CD,
B.CTRT_LDGR_MNG_NO,
B.ATFL_GRP_ID,
B.FYR,
B.EXPS_DV_CD,
B.PCUR_DV_CD,
B.CTRT_KND_CD,
B.CTRT_MTH_CD,
B.CTRT_TY_DTS_CD,
B.NEW_LTM_DV_CD,
B.CPRT_CTRT_DV_CD,
B.CTRT_TRGT_NM,
B.CTRT_OTLN_CN,
B.LCTN_NM,
B.CTRT_NO,
B.PCUR_MNG_NO,
B.PCUR_CHRG_DEPT_NM,
B.CGP_TELNO,
B.PCUR_FEE,
B.PODR_DV_CD,
B.FRST_SMZ_CTRT_AMT,
B.SMZ_CTRT_TOTT_AMT,
B.SMZ_CTRT_YMD,
B.BGCS_YMD,
B.CMCN_YMD,
B.CMCN_SCD_YMD,
B.RGET_YN,
B.DBT_BDN_AMT,
B.SMZ_CSTC_DYS,
B.PVCN_RSON_CD,
B.PVCN_RSON_SCLS_CD,
B.G2B_RCH_DT,
B.CTRT_FOM_CD,
B.MDFCN_YMD,
B.CTRT_CHRG_USR_ID,
B.BF_SMZ_CTRT_AMT,
B.DLY_RPN_AMT_RT,
B.FRST_CTRT_LDGR_NO,
B.DEPT_CD,
B.PCUR_PRG_DV_CD,
B.EXNT_CD,
B.MDFCN_DT,
B.MDFCN_USR_ID,
B.GIVE_MTH_CD,
B.LEAS_YN,
B.SMZ_CTRT_DGR,
B.SMZ_CTRT_DGR_EXI_YN,
B.CTRT_OPSL_DV_CD,
B.CTRT_MTH_DTS_DV_CD,
B.CTDM_DV_CD,
B.CTRT_CNLT_YN,
B.CTRT_CN,
B.PBN_TOTT_AMT,
B.RNSM_TOTT_AMT,
B.CMPS_MBIZ_CD,
B.LBC_TOTT_AMT,
B.SCGD_YN,
B.G2B_CK_NM,
B.G2B_GD_PAT_DSCRT,
B.G2B_GD_PAT_DSC_AMT,
B.GVSL_MTRL_DV_CD,
B.CSTC_DV_CD,
B.LGN_RGSTR_USR_ID,
B.LGN_RGSTR_DT,
B.FRST_RGSTR_USR_ID,
B.FRST_RGSTR_DT,
B.LAST_MDFCN_USR_ID,
B.LAST_MDFCN_DT
)
</update>
테이블 DDL.txt 파일을 보니 SMZ_CSTC_DYS 컬럼 타입이 NUMERIC(5, 0)으로 되어 있습니다.
그런데 MERGE INTO절에서는 VARCHAR 타입으로 되어 있어서 발생한 것으로 보입니다.
#[].smzCstcDys# AS "SMZ_CSTC_DYS",
--> CAST(#[].smzCstcDys# AS NUMERIC(5,0)) AS "SMZ_CSTC_DYS", 으로 변경하여 수행해보세요.