we are facing a data conversion error while executing a query in ODBC stage.The DB:oracle. we are using the NLS character map <ISO-8859-1>(project default).
There is one error with the week_st_dt column.The datatype of this column in source table is "Date" .In the query we are changing it to "Timestamp" datatype.The length of Timestamp is 38.
ERROR Statement:##W TDOR 000158 03:52:52(000) <main_program> The NLS character map <ISO-8859-1> specified may not be compatible with
the character set specified by NLS_LANG;
you may get unexpected results from your database.
>##E TFIP 000000 03:53:04(001) <Oracle_Enterprise_0> Error when checking operator: When binding output interface field "WEEK_START_DT" to field "WEEK_START_DT": No default type conversion from type "string[max=19]" to type "timestamp".
>##E TFSR 000019 03:53:04(002) <main_program> Could not check all operators because of previous error(s)
##W TFIP 000000 03:53:04(003) <Oracle_Enterprise_0> Error when checking operator: When binding output interface field "COUNTRY_CD" to field "COUNTRY_CD": Implicit conversion from source type "string[max=10]" to result type "string[max=8]": Possible truncation of variable length string.
>##E TCOS 000029 03:53:04(004) <main_program> Creation of a step finished with status = FAILED.
Code: Select all
SELECT A.PROMO_ID,A.COUNTRY_CD, WEEK_START_DT,A.PROMO_INDEX_NBR,ITEM_NBR from ITEM_GROUP_ASSN B,
(SELECT TI.PROMO_ID,TI.PROD_GROUP_TYP_ITEM_CD,TI.ITEM_SELECTION_TXT,TP.COUNTRY_CD,PR.PROMO_INDEX_NBR,TO_CHAR(PR.WEEK_START_DT, 'YYYY-MM-DD HH24:MI:SS') AS WEEK_START_DT
FROM INTREBOS.XFER_PROMOTION_ITEM_HIERARCHY TI, INTREBOS.TRANSFER_PROMOTION TP,INTREBOS.XFER_PROMOTION_WEEKLY_INDEX PR
WHERE TI. PROMO_ID=TP.PROMO_ID
AND PR.PROMO_ID=TP.PROMO_ID
AND TRUNC(TP.PROMO_WEEK_END_DT)>=(SELECT TRUNC(TO_DATE('1-JUL-2008')) - MAX(GLOBAL_PARM_QTY) * 7
FROM INTREBOS.ITEM_TRANSFER_GLOBAL_PARAMETER WHERE GLOBAL_PARM_CD = 'WOHIST'))A
WHERE A.PROD_GROUP_TYP_ITEM_CD = 'ITEM_GROUP' AND
B.ITEM_GROUP_CODE =A.ITEM_SELECTION_TXT
ORDER BY A.PROMO_ID,A.COUNTRY_CD, A.WEEK_START_DT,A.PROMO_INDEX_NBR
what should be the NLS datatype we should use to overcome this issue.