Error (38.1): PL / SQL: ORA-00932: Inconsistent data types: expected DATE got NUMBER
I have a problem with some code Oracle SQL
that I am running and I canβt figure out what the problem is for my whole life. I have this code that works:
create or replace
PROCEDURE XP_CLOSE_SCORECARD (pSC_VER_ID IN NUMBER)
AS
---------------------------------------------------------------------------------------------------
-- XP_CLOSE_SCORECARD
--
-- DESCRIPTION:
-- Called when SC wkflw is marked complete.
--
-- CALLED BY:
-- Scorecard workflow
--
-- MODIFICATION HISTORY:
-- 2008-06-24 CJW Setup.
-- 2008-08-22 CJW Change to pull from views.
-- 2009-02-09 CJW Enhancements.
-- 2010-08-18 EC BVD update. 'STD_INDS_CODE' changed to SIC_COD9 code
-- 2013-01-17 EC Version 7 updates. Remvoed logging to another table as cuases problems for Smartest to Support and investigate error.
---------------------------------------------------------------------------------------------------
vCP_ID NUMBER;
vErrCode NUMBER(38);
vErrMsg VARCHAR2(250);
vPROCNAME VARCHAR2(250);
BEGIN
vPROCNAME := 'XP_CLOSE_SCORECARD';
---------------------------------------------------------------------------------------------------
-- Copy data to OBX_CRM
---------------------------------------------------------------------------------------------------
select CP_ID INTO vCP_ID from sc_version sv inner join scorecard sc on sc.id = sv.scorecard_id
and sv.id = pSC_VER_ID;
INSERT INTO OBX_CRM_V2
(
OBX_CRM_ID,
CPTY_KEY,
CPTY_NAME,
DB_NUMBER,
SIC_CODE,
CPTY_SCORE,
APPROVED_LIMIT,
SUGGESTED_LIMIT,
LIMIT_EXPIRATION,
LIMIT_COMMENT,
APPROVAL_0,APPROVAL_0_DT,
APPROVAL_1,APPROVAL_1_DT,
APPROVAL_2,APPROVAL_2_DT,
APPROVAL_3,APPROVAL_3_DT,
APPROVAL_4,APPROVAL_4_DT,
APPROVAL_5,APPROVAL_5_DT,
APPROVAL_6,APPROVAL_6_DT,
APPROVAL_7,APPROVAL_7_DT,
CRDT_INS,
CRDT_INS_LIMIT,
CRDT_INS_COVERAGE,
CRDT_INS_EXPIRY,
CPTY_COMMENT,
CREATED_BY,
CREATED_TS
)
SELECT
NULL AS OBX_CRM_ID,
TO_CHAR(CP.COUNTERPARTY_NUMBER) AS CPTY_KEY,
TO_CHAR(CP.NAME) AS CPTY_NAME,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10079) AS DB_NUMBER,
(SELECT TO_CHAR(MAX(D.ELEMENT_VALUE)) FROM DNB_COMPANY_DETAIL D WHERE D.COMP_ID = CP.ID
AND D.ELEMENT_CODE = 'SIC_COD9') AS SIC_CODE,
(SELECT TO_CHAR(SCORE) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS CPTY_SCORE,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10071) AS APPROVED_LIMIT,
(SELECT TO_NUMBER(SUGG_CRED_LIM) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS SUGGESTED_LIMIT,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10086) AS LIMIT_EXPIRATION,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRT_APC') AND OWNER_ID = CP.ID) AS LIMIT_COMMENT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10077) AS APPROVAL_0,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10088) AS APPROVAL_0_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10130) AS APPROVAL_1,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10131) AS APPROVAL_1_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10072) AS APPROVAL_2,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10085) AS APPROVAL_2_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10070) AS APPROVAL_3,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10083) AS APPROVAL_3_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10075) AS APPROVAL_4,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10082) AS APPROVAL_4_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10078) AS APPROVAL_5,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10081) AS APPROVAL_5_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10073) AS APPROVAL_6,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10080) AS APPROVAL_6_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10128) AS APPROVAL_7,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10127) AS APPROVAL_7_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS') AND OWNER_ID = CP.ID) AS CRDT_INS,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_AMT') AND OWNER_ID = CP.ID) AS CRDT_INS_LIMIT,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_COV') AND OWNER_ID = CP.ID) AS CRDT_INS_COVERAGE,
(SELECT TO_DATE(VALUE_DATE) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_VAL') AND OWNER_ID = CP.ID) AS CRDT_INS_EXPIRY,
TO_CHAR(CP.COMMENTS) AS CPTY_COMMENT,
TO_CHAR(vPROCNAME) AS CREATED_BY,
SYSDATE AS CREATED_TS
FROM
COUNTERPARTY CP
WHERE
CP.ID = vCP_ID
;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
END;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
And this code doesn't:
create or replace
PROCEDURE XP_CLOSE_SCORECARD (pSC_VER_ID IN NUMBER)
AS
---------------------------------------------------------------------------------------------------
-- XP_CLOSE_SCORECARD
--
-- DESCRIPTION:
-- Called when SC wkflw is marked complete.
--
-- CALLED BY:
-- Scorecard workflow
--
-- MODIFICATION HISTORY:
-- 2008-06-24 CJW Setup.
-- 2008-08-22 CJW Change to pull from views.
-- 2009-02-09 CJW Enhancements.
-- 2010-08-18 EC BVD update. 'STD_INDS_CODE' changed to SIC_COD9 code
-- 2013-01-17 EC Version 7 updates. Remvoed logging to another table as cuases problems for Smartest to Support and investigate error.
---------------------------------------------------------------------------------------------------
vCP_ID NUMBER;
vErrCode NUMBER(38);
vErrMsg VARCHAR2(250);
vPROCNAME VARCHAR2(250);
BEGIN
vPROCNAME := 'XP_CLOSE_SCORECARD';
---------------------------------------------------------------------------------------------------
-- Copy data to OBX_CRM
---------------------------------------------------------------------------------------------------
select CP_ID INTO vCP_ID from sc_version sv inner join scorecard sc on sc.id = sv.scorecard_id
and sv.id = pSC_VER_ID;
INSERT INTO OBX_CRM_V2
(
OBX_CRM_ID,
CPTY_KEY,
CPTY_NAME,
DB_NUMBER,
SIC_CODE,
CPTY_SCORE,
APPROVED_LIMIT,
SUGGESTED_LIMIT,
LIMIT_EXPIRATION,
LIMIT_COMMENT,
APPROVAL_0,APPROVAL_0_DT,
APPROVAL_1,APPROVAL_1_DT,
APPROVAL_2,APPROVAL_2_DT,
APPROVAL_3,APPROVAL_3_DT,
APPROVAL_4,APPROVAL_4_DT,
APPROVAL_5,APPROVAL_5_DT,
APPROVAL_6,APPROVAL_6_DT,
APPROVAL_7,APPROVAL_7_DT,
CRDT_INS,
CRDT_INS_LIMIT,
CRDT_INS_COVERAGE,
CRDT_INS_EXPIRY,
CPTY_COMMENT,
CREATED_BY,
CREATED_TS,
S_RAIVA,
S_RCSDA,
S_RCEDA,
S_ARPT,
S_ARPM,
S_GAEVA
)
SELECT
NULL AS OBX_CRM_ID,
TO_CHAR(CP.COUNTERPARTY_NUMBER) AS CPTY_KEY,
TO_CHAR(CP.NAME) AS CPTY_NAME,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10079) AS DB_NUMBER,
(SELECT TO_CHAR(MAX(D.ELEMENT_VALUE)) FROM DNB_COMPANY_DETAIL D WHERE D.COMP_ID = CP.ID
AND D.ELEMENT_CODE = 'SIC_COD9') AS SIC_CODE,
(SELECT TO_CHAR(SCORE) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS CPTY_SCORE,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10071) AS APPROVED_LIMIT,
(SELECT TO_NUMBER(SUGG_CRED_LIM) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS SUGGESTED_LIMIT,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10086) AS LIMIT_EXPIRATION,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRT_APC') AND OWNER_ID = CP.ID) AS LIMIT_COMMENT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10077) AS APPROVAL_0,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10088) AS APPROVAL_0_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10130) AS APPROVAL_1,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10131) AS APPROVAL_1_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10072) AS APPROVAL_2,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10085) AS APPROVAL_2_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10070) AS APPROVAL_3,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10083) AS APPROVAL_3_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10075) AS APPROVAL_4,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10082) AS APPROVAL_4_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10078) AS APPROVAL_5,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10081) AS APPROVAL_5_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10073) AS APPROVAL_6,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10080) AS APPROVAL_6_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10128) AS APPROVAL_7,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10127) AS APPROVAL_7_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS') AND OWNER_ID = CP.ID) AS CRDT_INS,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_AMT') AND OWNER_ID = CP.ID) AS CRDT_INS_LIMIT,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_COV') AND OWNER_ID = CP.ID) AS CRDT_INS_COVERAGE,
(SELECT TO_DATE(VALUE_DATE) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_VAL') AND OWNER_ID = CP.ID) AS CRDT_INS_EXPIRY,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10286) AS S_RCSDA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10287) AS S_RCEDA,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10288) AS S_ARPT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10289) AS S_ARPM,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA,
TO_CHAR(CP.COMMENTS) AS CPTY_COMMENT,
TO_CHAR(vPROCNAME) AS CREATED_BY,
SYSDATE AS CREATED_TS
FROM
COUNTERPARTY CP
WHERE
CP.ID = vCP_ID
;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
END;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
The difference between the two is adding:
S_RAIVA, S_RCSDA, S_RCEDA, S_ARPT, S_ARPM, S_GAEVA
Near the top and down below.
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10286) AS S_RCSDA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10287) AS S_RCEDA,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10288) AS S_ARPT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10289) AS S_ARPM,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA,
I am getting the error:
Error(38,1): PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
when i try to compile it but i dont understand why. Line 38:
INSERT INTO OBX_CRM_V2
and doesn't give me an explanation for where the problem exists, and the extra lines follow the same syntax as the code, which works as far as I can tell. Any help would be greatly appreciated as I am a complete newbie to this.
+3
source to share
2 answers
You are inserting into the table data OBX_CRM_V2
returned SELECT
. The problem is that the order of the columns doesn't match. The new columns are listed at the end INSERT
, but not at the very end of the main one SELECT
.
What's going on here is that new operators SELECT
at the end of the code will try to insert in CPTY_COMMENT, CREATED_BY, CREATED_TS, S_RAIVA, S_RCSDA, S_RCEDA
, not in S_RAIVA, S_RCSDA, S_RCEDA, S_ARPT, S_ARPM, S_GAEVA
what you need.
Should work after reordering:
create or replace
PROCEDURE XP_CLOSE_SCORECARD (pSC_VER_ID IN NUMBER)
AS
---------------------------------------------------------------------------------------------------
-- XP_CLOSE_SCORECARD
--
-- DESCRIPTION:
-- Called when SC wkflw is marked complete.
--
-- CALLED BY:
-- Scorecard workflow
--
-- MODIFICATION HISTORY:
-- 2008-06-24 CJW Setup.
-- 2008-08-22 CJW Change to pull from views.
-- 2009-02-09 CJW Enhancements.
-- 2010-08-18 EC BVD update. 'STD_INDS_CODE' changed to SIC_COD9 code
-- 2013-01-17 EC Version 7 updates. Remvoed logging to another table as cuases problems for Smartest to Support and investigate error.
---------------------------------------------------------------------------------------------------
vCP_ID NUMBER;
vErrCode NUMBER(38);
vErrMsg VARCHAR2(250);
vPROCNAME VARCHAR2(250);
BEGIN
vPROCNAME := 'XP_CLOSE_SCORECARD';
---------------------------------------------------------------------------------------------------
-- Copy data to OBX_CRM
---------------------------------------------------------------------------------------------------
select CP_ID INTO vCP_ID from sc_version sv inner join scorecard sc on sc.id = sv.scorecard_id
and sv.id = pSC_VER_ID;
INSERT INTO OBX_CRM_V2
(
OBX_CRM_ID,
CPTY_KEY,
CPTY_NAME,
DB_NUMBER,
SIC_CODE,
CPTY_SCORE,
APPROVED_LIMIT,
SUGGESTED_LIMIT,
LIMIT_EXPIRATION,
LIMIT_COMMENT,
APPROVAL_0,APPROVAL_0_DT,
APPROVAL_1,APPROVAL_1_DT,
APPROVAL_2,APPROVAL_2_DT,
APPROVAL_3,APPROVAL_3_DT,
APPROVAL_4,APPROVAL_4_DT,
APPROVAL_5,APPROVAL_5_DT,
APPROVAL_6,APPROVAL_6_DT,
APPROVAL_7,APPROVAL_7_DT,
CRDT_INS,
CRDT_INS_LIMIT,
CRDT_INS_COVERAGE,
CRDT_INS_EXPIRY,
CPTY_COMMENT,
CREATED_BY,
CREATED_TS,
S_RAIVA,
S_RCSDA,
S_RCEDA,
S_ARPT,
S_ARPM,
S_GAEVA
)
SELECT
NULL AS OBX_CRM_ID,
TO_CHAR(CP.COUNTERPARTY_NUMBER) AS CPTY_KEY,
TO_CHAR(CP.NAME) AS CPTY_NAME,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10079) AS DB_NUMBER,
(SELECT TO_CHAR(MAX(D.ELEMENT_VALUE)) FROM DNB_COMPANY_DETAIL D WHERE D.COMP_ID = CP.ID
AND D.ELEMENT_CODE = 'SIC_COD9') AS SIC_CODE,
(SELECT TO_CHAR(SCORE) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS CPTY_SCORE,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10071) AS APPROVED_LIMIT,
(SELECT TO_NUMBER(SUGG_CRED_LIM) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS SUGGESTED_LIMIT,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10086) AS LIMIT_EXPIRATION,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRT_APC') AND OWNER_ID = CP.ID) AS LIMIT_COMMENT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10077) AS APPROVAL_0,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10088) AS APPROVAL_0_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10130) AS APPROVAL_1,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10131) AS APPROVAL_1_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10072) AS APPROVAL_2,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10085) AS APPROVAL_2_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10070) AS APPROVAL_3,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10083) AS APPROVAL_3_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10075) AS APPROVAL_4,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10082) AS APPROVAL_4_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10078) AS APPROVAL_5,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10081) AS APPROVAL_5_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10073) AS APPROVAL_6,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10080) AS APPROVAL_6_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10128) AS APPROVAL_7,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10127) AS APPROVAL_7_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS') AND OWNER_ID = CP.ID) AS CRDT_INS,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_AMT') AND OWNER_ID = CP.ID) AS CRDT_INS_LIMIT,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_COV') AND OWNER_ID = CP.ID) AS CRDT_INS_COVERAGE,
(SELECT TO_DATE(VALUE_DATE) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_VAL') AND OWNER_ID = CP.ID) AS CRDT_INS_EXPIRY,
TO_CHAR(CP.COMMENTS) AS CPTY_COMMENT,
TO_CHAR(vPROCNAME) AS CREATED_BY,
SYSDATE AS CREATED_TS,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10286) AS S_RCSDA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10287) AS S_RCEDA,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10288) AS S_ARPT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10289) AS S_ARPM,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA
FROM
COUNTERPARTY CP
WHERE
CP.ID = vCP_ID
;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
END;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
+2
source to share
Check table description for OBX_CRM_V2
. You are trying to insert a number into a date column.
Either S_RAIVA
or S_GAEVA
your INSERT
- the two NUMBERs
are actually trying to insert a value in the column DATE
.
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
or
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA,
A simple example for this.
insert into employee(empno,hiredate) values (1,100);
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
So, make sure the order of the values ββin INSERT
matches the order of the column in the table.
+2
source to share