Convertions
Moderators: chulett, rschirm, roy
Convertions
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,
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,
Re: Convertions
You need to change varchar to timestamp. Type conversion is required.shilpa79 wrote: 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".
Same as previous reply.shilpa79 wrote:
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".
Increase the target size to 17 (1 for decimal sign and 1 for decimal point)shilpa79 wrote:
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].
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 wrote:
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: Convertions
DSguru2B wrote:You need to change varchar to timestamp. Type conversion is required.shilpa79 wrote: 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".
Same as previous reply.shilpa79 wrote:
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".Increase the target size to 17 (1 for decimal sign and 1 for decimal point)shilpa79 wrote:
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].
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 wrote:
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.
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,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
No, I am not designing this Job in server its a PX job.ray.wurlod wrote: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 ...
I tried with the above results still while compiling its throwing me the error.
Thanks,
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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.
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.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
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,
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,
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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.
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.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
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.![Cool 8)](./images/smilies/icon_cool.gif)
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.
![Cool 8)](./images/smilies/icon_cool.gif)
Thanks and Regards!!
dspxlearn
dspxlearn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
shilpa79 wrote:No, I am not designing this Job in server its a PX job.ray.wurlod wrote: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 ...
I tried with the above results still while compiling its throwing me the error.
Thanks,
Ray Wurlod wrote:you even marked this as a server job
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA