Convertions

[shilpa79]

I am extracting data from Orcale and generating and XML file

I am having problem with these four columns from source:
INVOICE_CREATION_DATE Varchar
DELIVERY_DATE_TIME Varchar
INVOICE_QUANTITY Decimal(15)
AMOUNT Decimal(13,2)


Error messages:-------------------------------------------------------------------

1)Copy_pre_xform: Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_CREATION_DATE" to field "INVOICE_CREATION_DATE": No default type conversion from type "string[max=38]" to type "timestamp".

2)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "DELIVERY_DATE_TIME" to field "DELIVERY_DATE_TIME": No default type conversion from type "string[max=38]" to type "timestamp".

3)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_QUANTITY" to field "INVOICE_QUANTITY": Implicit conversion from source type "decimal[15,0]" to result type "ustring[max=15]": Not enough room in ustring for decimal[15,0].

4)SRC_franzP_Oracle: When checking operator: When binding output interface field "AMOUNT" to field "AMOUNT": Implicit conversion from source type "decimal[38,10]" to result type "decimal[13,2]": Possible range/precision limitation.
----------------------------------------------------------------------------------

Thanks,

[DSguru2B]

1)Copy_pre_xform: Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_CREATION_DATE" to field "INVOICE_CREATION_DATE": No default type conversion from type "string[max=38]" to type "timestamp".

You need to change varchar to timestamp. Type conversion is required.

2)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "DELIVERY_DATE_TIME" to field "DELIVERY_DATE_TIME": No default type conversion from type "string[max=38]" to type "timestamp".

Same as previous reply.

3)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_QUANTITY" to field "INVOICE_QUANTITY": Implicit conversion from source type "decimal[15,0]" to result type "ustring[max=15]": Not enough room in ustring for decimal[15,0].

Increase the target size to 17 (1 for decimal sign and 1 for decimal point)

4)SRC_franzP_Oracle: When checking operator: When binding output interface field "AMOUNT" to field "AMOUNT": Implicit conversion from source type "decimal[38,10]" to result type "decimal[13,2]": Possible range/precision limitation.

Your trying to fit a size 38 decimal number into a size 13 decimal number column. Please fix the target size to match the source.

[shilpa79]

1)Copy_pre_xform: Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_CREATION_DATE" to field "INVOICE_CREATION_DATE": No default type conversion from type "string[max=38]" to type "timestamp".

You need to change varchar to timestamp. Type conversion is required.

2)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "DELIVERY_DATE_TIME" to field "DELIVERY_DATE_TIME": No default type conversion from type "string[max=38]" to type "timestamp".

Same as previous reply.

3)Error when checking operator: When binding output schema variable "outRec": When binding output interface field "INVOICE_QUANTITY" to field "INVOICE_QUANTITY": Implicit conversion from source type "decimal[15,0]" to result type "ustring[max=15]": Not enough room in ustring for decimal[15,0].

Increase the target size to 17 (1 for decimal sign and 1 for decimal point)

4)SRC_franzP_Oracle: When checking operator: When binding output interface field "AMOUNT" to field "AMOUNT": Implicit conversion from source type "decimal[38,10]" to result type "decimal[13,2]": Possible range/precision limitation.

Your trying to fit a size 38 decimal number into a size 13 decimal number column. Please fix the target size to match the source.

Before it was timestamp and then later I chnaged to varchar because when i say say view data it was throwing erorr .

FOr the other two columns I have given the same length still its throwin me the same error.

for Amount field its comming as 13,2 from the source not 38 . I have given the same for the target.

I am getting error while compiling the job

##I TOSH 000002 14:10:12(001) <main_program> orchgeneral: loaded
##I TOSH 000002 14:10:12(002) <main_program> orchsort: loaded
##I TOSH 000002 14:10:12(003) <main_program> orchstats: loaded
##I TFSC 000001 14:10:12(006) <main_program> APT configuration file: /vend/dsadm/Ascential/DataStage/Configurations/default.apt
##W TCOS 000049 14:10:13(000) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##W TFCP 000000 14:10:13(002) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##W TFCP 000000 14:10:13(003) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W TFCP 000000 14:10:13(004) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W TFCP 000000 14:10:13(005) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W TFCP 000025 14:10:13(006) <transform> Error when checking composite operator: Converting ustring to string using codepage UTF-8.
##W TFCP 000000 14:10:13(007) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "String".
##W TFCP 000025 14:10:13(008) <transform> Error when checking composite operator: Converting ustring to string using codepage UTF-8.
##W TFCP 000000 14:10:13(009) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "String".
##W TFCP 000000 14:10:13(010) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##E TFCP 000026 14:10:13(011) <transform> Error when checking composite operator: Setting null to this non-nullable field: remarks.
##E TFSR 000019 14:10:13(012) <main_program> Could not check all operators because of previous error(s)
##E TCOS 000029 14:10:13(013) <main_program> Creation of a step finished with status = FAILED. (xxxxxtransform.X_xxxxxx)


Thanks,

[ray.wurlod]

Not all conversions are automatic as in server jobs.

Your mindset is still on server jobs - you even marked this as a server jobs. The error message codes indicate that it isn't.

You need explicitly to address each of the issues, with data type conversion functions and appropriate data types.

There are no short cuts.

[shilpa79]

Not all conversions are automatic as in server jobs.

Your mindset is still on server jobs - you even marked this as a server jobs. The error message codes indicate that it isn't.

You need expli ...

No, I am not designing this Job in server its a PX job.

I tried with the above results still while compiling its throwing me the error.

Thanks,

[us1aslam1us]

OK.
First thing are you able to view data with the original datatype settings? Second, what functions are you using to do the explicit conversion from VARCHAR to TIMESTAMP and are you using the correct format (Looking at your other posts you got this 'T' character issue in your timestamps). Third, For the INVOICE_QUANTITY increase the target size to 17 as suggested.
Fourth, What is the data type for AMOUNT field in source and in target? As suggested by DSguru you need to fix the target size of this field.

Still if it is not resolved the best way will be to analyze your source data, instead of you XML output use a flat file and chack the flow of data.

[shilpa79]

1)As wht dsguru said I increased the INVOICE_QUANTITY column length to 17
Source sample data :" 000000000007601"
source sql type is Varchar(15) ----- Xml Varchar(17)

Error1:INVOICE_QUANTITY floating point decimal is not fully supported; adjusting the scale.
Error2:When checking operator: When binding output interface field "INVOICE_QUANTITY" to field "INVOICE_QUANTITY": Implicit conversion from source type "decimal[38,10]" to result type "decimal[15,0]": Possible range/precision limitation.

2) For the Amount field its generating corect value in the xml file but still throwing an

Error1:AMOUNT floating point decimal is not fully supported; adjusting the scale
Error2:checking operator: When binding output interface field "AMOUNT" to field "AMOUNT": Implicit conversion from source type "decimal[38,10]" to result type "decimal[13,2]": Possible range/precision limitation.
the sqltype for this column is
Source :Decimal (13,2) Eg: source sample data :"00000021955.11"
XMl file :Decimal(13,2)

3)dates delivery date and invoice date
source sql type is timestamp and Source sample date is "04/13/2007"
XMl file format is varchar

In transformation i have given
"If IsNotNull(xform.DELIVERY_DATE_TIME) then TimestampToString(xform.DELIVERY_DATE_TIME ,"%dd-%mmm-%yyyy %hh:%nn:%ss") else SetNull()" ---- for both the columns

Errors:All these fields ar nullable in source and target still the same issue

1)Conversion error calling conversion routine timestamp_from_string data may have been lost

2)When validating export schema: At field "INVOICE_CREATION_DATE": Exporting nullable field without null handling properties

3)When checking operator: When validating export schema: At field "DELIVERY_DATE_TIME": Exporting nullable field without null handling properties

4)When checking operator: When validating export schema: At field "INVOICE_QUANTITY": Exporting nullable field without null handling properties

5)When checking operator: When validating export schema: At field "AMOUNT": Exporting nullable field without null handling properties

Thanks,

[us1aslam1us]

1. Read INVOICE_QUANTITY as Decimal(15,0) or Decimal(17,2). There seems to be an issue with your source table.
2. Change AMOUNT field as decimal(38,10) both in source and target.
3. Your source timestamp fields are missing the Time part of it so concatenate the time part to the field or read that as 'Date' .

Before doing anything first check whether you are able to read the data from source or not later Check for Null handling then do the required conversions.

What application is creating that source table? Check the Metadata there.

[dspxlearn]

Hi Shilpa,

Just try by casting the fields from the source itself.
Eg: in the sql use CAST(INVOICE_QUANTITY as date), CAST(AMOUNT as decimal(31,11)).
Then in the datastage job use datatypes as date, decimal(31,11) respectively.

It will work if you don't have any issues in modifying the sql.

[chulett]

You really want to suggest they CAST the INVOICE_QUANTITY to a date?

[dspxlearn]

Ooops!! That was a just a typo.

[ray.wurlod]

Not all conversions are automatic as in server jobs.

Your mindset is still on server jobs - you even marked this as a server job. The error message codes indicate that it isn't.

You need expli ...

No, I am not designing this Job in server its a PX job.

I tried with the above results still while compiling its throwing me the error.

Thanks,

you even marked this as a server job

[shilpa79]

Thanks for all your inputs. I will try them and get back to you......................

[shilpa79]

[2. Change AMOUNT field as decimal(38,10) both in source and target.
[/quote]

If I give decimal(38,10) for the source column it giving me as
00000000000.00

Thanks

[us1aslam1us]

Try to use cast function and read that as a varchar.