Data conversion issue.

[vinovenkat]

Hi ,
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.

[priyadarshikunal]

Hi ,
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.

Why do you think that this problem is due to NLS.
There is nothing unusual in this.

Code: Select all

TO_CHAR(PR.WEEK_START_DT, 'YYYY-MM-DD HH24:MI:SS') AS WEEK_START_DT 

you are converting the date in to String and expecting Datastage to read it as TimeStamp.

Either remove to_char from your Query or use To_date convert the string in to date or read it as string.

[ray.wurlod]

When a thread is marked as Resolved the convention is to make a final post explaining to future searchers exactly how it was Resolved. Please do so.