Good morning, I need tour help please, in a Datastage Environment a job yields this Error:
Lkp_OferValCargo,0: The OCI function OCIStmtFetch2 returned status -1. Error code: 1722, Error message: ORA-01722: Invalid number. (CC_OraStatement::fetch, file CC_OraStatement.cpp, line 1,853)
Im not a DBA so I dont know how to catch which data is invalid, but to start off, i got this query but the time it was running:
WITH SERVICIOS AS (
SELECT A.SUBS_ID
,A.OFFERING_ID
,CASE WHEN A.OFFERING_ID = B.OFFERING_ID AND A.PRIMARY_FLAG = 'Y' THEN 1 ELSE 0 END SERVPRIM
FROM #Param_ODS.$Edw_Ods#.INF_OFFERING_INST A
,#Param_ODS.$Edw_Ods#.INF_SUBSCRIBER B
WHERE A.SUBS_ID = B.SUBS_ID
AND B.SUBS_ID = ORCHESTRATE.SUBS_ID
), OFERTA_SECUNDARIA AS (
SELECT DISTINCT
OFER_PRI.SUBS_ID
,OFER_SEC.OFFERING_ID
,OFER_PRI.OFFERING_ID OFFERING_ID_P
,JSON_VALUE(PRT.RELA_NAME,'$.es_CO') TIPO_RELACION
FROM #Param_ODS.$Edw_Ods#.PM_ENTITY_RELATION OER
,#Param_ODS.$Edw_Ods#.PM_RELATIONTYPE PRT
,(SELECT SUBS_ID, OFFERING_ID FROM SERVICIOS WHERE SERVPRIM = 1) OFER_PRI
,(SELECT SUBS_ID, OFFERING_ID FROM SERVICIOS WHERE SERVPRIM = 0) OFER_SEC
WHERE OER.RELA_TYPE = PRT.RELA_TYPE
AND OER.ORIGIN_ENTRY_TYPE = 'O'
AND OER.DEST_ENTRY_TYPE = 'O'
AND JSON_VALUE(PRT.RELA_NAME,'$.es_CO') IN ('Obligatorio','Opcional','Defecto')
AND OFER_PRI.SUBS_ID = OFER_SEC.SUBS_ID
AND OER.ORIGIN_ENTRY_ID = OFER_PRI.OFFERING_ID
AND OER.DEST_ENTRY_ID = OFER_SEC.OFFERING_ID
), OFERTA_COMPLETA AS (
SELECT SUBS_ID, OFFERING_ID_P, OFFERING_ID, TIPO_RELACION FROM OFERTA_SECUNDARIA
UNION
SELECT SUBS_ID, OFFERING_ID OFFERING_ID_P, OFFERING_ID, 'Plan' TIPO_RELACION FROM SERVICIOS WHERE SERVPRIM = 1
) SELECT TRIM(/*REPLACE(TO_CHAR(*/SUM(NVL(MONFEE.VALOR_PAGAR,0))/*,'999G999G990G999'),',','.')*/) AS VALOR_PAGAR
,TRIM(/*REPLACE(TO_CHAR(*/SUM(CASE WHEN OFFCOM.TIPO_RELACION IN ('Defecto','Plan') THEN NVL(MONFEE.VALOR_PAGAR,0) ELSE 0 END)/*,'999G999G990G999'),',','.')*/) AS CARGO_BASICO
,TRIM(/*REPLACE(TO_CHAR(*/SUM(CASE WHEN OFFCOM.TIPO_RELACION IN ('Defecto','Plan') THEN NVL(MONFEE.VALOR_PAGAR,0) ELSE 0 END)/*,'999G999G990G999'),',','.')*/) AS TOTAL_CARGO_BASICO
,TRIM(/*REPLACE(TO_CHAR(*/SUM(NVL(MONFEE.VALOR_PAGAR,0))/*,'999G999G990G999'),',','.')*/) AS MENSUALIDAD_TOTAL
FROM OFERTA_COMPLETA OFFCOM
,(SELECT PEA.ENTITY_ID
,TO_NUMBER(JSON_VALUE(PEA.ATTR_VALUE, '$.VALUE')) VALOR_PAGAR
,ROW_NUMBER() OVER(PARTITION BY PEA.ENTITY_ID ORDER BY PEA.ENTITY_ID DESC ) AS PEA_NUM_REG
FROM #Param_ODS.$Edw_Ods#.PM_ENTITY_ATTR PEA
,#Param_ODS.$Edw_Ods#.SYS_PROPERTY PTY
WHERE PEA.ATTR_ID = PTY.PROP_ID
AND PTY.PROP_CODE = 'C_O_MONTHLY_FEE'
AND PEA.ENTITY_TYPE = 'O'
AND JSON_VALUE(PEA.ATTR_VALUE, '$.VALUE') <> 'NA') MONFEE
WHERE OFFCOM.OFFERING_ID = MONFEE.ENTITY_ID(+)
AND NVL(MONFEE.PEA_NUM_REG(+),1) = 1
AND OFFCOM.SUBS_ID = ORCHESTRATE.SUBS_ID
AND OFFCOM.OFFERING_ID_P = ORCHESTRATE.OFFERING_ID
GROUP BY OFFCOM.SUBS_I
Is there any technique or procedure used to debug this query and catch up the problematic or invalid data?
I appreciate your help in advanced