Reg: NULL values in comma delimited file
Moderators: chulett, rschirm, roy
Reg: NULL values in comma delimited file
Hi Everybody ,
I am working on datastage from the past 3 months.We are working on DATASTAGE 8.0.1 version on LINUX box.
I am facing some problem in NULL handling .My scenario is as below
I am having a sequential file .
its a COMMA DELIMITED.
12,99,jkl,10082008,a
12,,mno,,a
13,20,klo,11062008,b
So i am using DECIMAL ( 2,0 ) to read the second field field ( Because in database it is mentioned as NUMBER(2,0).
My requirement is to load NULL for any field if it doesnt have any value in input file.
Since it is a comma delimited file with null values in some of the fields ,I am using NULL FIELD VALUE as ''.Through this approach i am able to read the file .while reading that data i am able to see 'NULL' for fields which doesnt have value in input file.But while inserting into oracle i am getting the below err.
value larger than specified precision allowed for this column
I think we are getting this error because of this below warning
When validating export schema: At field "Field_name": "null_field" length (5) must match field's fixed width (4)
I really have no idea about this warning.What perplexed me more is why its giving length(5) must match fields fixed width(4).
I am working on datastage from the past 3 months.We are working on DATASTAGE 8.0.1 version on LINUX box.
I am facing some problem in NULL handling .My scenario is as below
I am having a sequential file .
its a COMMA DELIMITED.
12,99,jkl,10082008,a
12,,mno,,a
13,20,klo,11062008,b
So i am using DECIMAL ( 2,0 ) to read the second field field ( Because in database it is mentioned as NUMBER(2,0).
My requirement is to load NULL for any field if it doesnt have any value in input file.
Since it is a comma delimited file with null values in some of the fields ,I am using NULL FIELD VALUE as ''.Through this approach i am able to read the file .while reading that data i am able to see 'NULL' for fields which doesnt have value in input file.But while inserting into oracle i am getting the below err.
value larger than specified precision allowed for this column
I think we are getting this error because of this below warning
When validating export schema: At field "Field_name": "null_field" length (5) must match field's fixed width (4)
I really have no idea about this warning.What perplexed me more is why its giving length(5) must match fields fixed width(4).
gikjpjj
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your field width is defined as 4 and therefore whatever you have specified as the null field value must have precisely four characters. Apparently what you have specified for null field value contains five characters.
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.
Thank you ray for your response.
Here i am using comma delimited file .
seq ------> transformer -----> oracle
I specified following properties in seq stage like
Field delimiter ------ comma
record delimiter ---- none.
I specified all the fields as char or varchar.I am not specifying any length in input stage .
I am able to read the data.But I am not understanding how to insert NULL in the fields which have no value in the input seq stage .
In transformer I gave
But still giving error at oracle level
Its giving When checking operator: When validating export schema: At field "YEAR": "null_field" length (5) must match field's fixed width (4)
In input stage i have not mentioned any length.
If i do one to one loading without any transformation , its inserting 0 instead of NULL in number and decimal fields.
I request somebody to suggest .
Thanks in advance
Here i am using comma delimited file .
seq ------> transformer -----> oracle
I specified following properties in seq stage like
Field delimiter ------ comma
record delimiter ---- none.
I specified all the fields as char or varchar.I am not specifying any length in input stage .
I am able to read the data.But I am not understanding how to insert NULL in the fields which have no value in the input seq stage .
In transformer I gave
Code: Select all
if linkname.colname="" then setnull() else linkname.colname.
Its giving When checking operator: When validating export schema: At field "YEAR": "null_field" length (5) must match field's fixed width (4)
In input stage i have not mentioned any length.
If i do one to one loading without any transformation , its inserting 0 instead of NULL in number and decimal fields.
I request somebody to suggest .
Thanks in advance
gikjpjj
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Reg: NULL values in comma delimited file
Can you post your schema?199542 wrote:Hi Everybody ,
I am working on datastage from the past 3 months.We are working on DATASTAGE 8.0.1 version on LINUX box.
I am facing some problem in NULL handling .My scenario is as below
I am having a sequential file .
its a COMMA DELIMITED.
12,99,jkl,10082008,a
12,,mno,,a
13,20,klo,11062008,b
So i am using DECIMAL ( 2,0 ) to read the second field field ( Because in database it is mentioned as NUMBER(2,0).
My requirement is to load NULL for any field if it doesnt have any value in input file.
Since it is a comma delimited file with null values in some of the fields ,I am using NULL FIELD VALUE as ''.Through this approach i am able to read the file .while reading that data i am able to see 'NULL' for fields which doesnt have value in input file.But while inserting into oracle i am getting the below err.
value larger than specified precision allowed for this column
I think we are getting this error because of this below warning
When validating export schema: At field "Field_name": "null_field" length (5) must match field's fixed width (4)
I really have no idea about this warning.What perplexed me more is why its giving length(5) must match fields fixed width(4).
Re: Reg: NULL values in comma delimited file
I mean for the input stage.Can you post your schema?
Hi everyone ,
Thank you for responding to my query.My input schema is as follows
record
{final_delim=end, delim=',', quote=none}
(
YEAR:nullable string;
QTR:nullable string;
FILG_DT:nullable string;
A:nullable string;
B:nullable string;
C:nullable string;
S:nullable string;
L:nullable string;
P:nullable string;
M:nullable string;
T:nullable string;
FCTR_TYP:nullable string;
FCTR_TYP_MDFR:nullable string;
DEFLT_IND:nullabe string;
FACTRY_VAL:nullable string;
)
Here Year , FCTR_TYP_MDFR and DEFLT_IND are number(2,0) in database .I am specify as char.I want NULL to be loaded when there is no data in the input file .
like 2008,1,11,abc,,,,,,,,,,,
Hope the information provided is sufficient .
Thank you
Thank you for responding to my query.My input schema is as follows
record
{final_delim=end, delim=',', quote=none}
(
YEAR:nullable string;
QTR:nullable string;
FILG_DT:nullable string;
A:nullable string;
B:nullable string;
C:nullable string;
S:nullable string;
L:nullable string;
P:nullable string;
M:nullable string;
T:nullable string;
FCTR_TYP:nullable string;
FCTR_TYP_MDFR:nullable string;
DEFLT_IND:nullabe string;
FACTRY_VAL:nullable string;
)
Here Year , FCTR_TYP_MDFR and DEFLT_IND are number(2,0) in database .I am specify as char.I want NULL to be loaded when there is no data in the input file .
like 2008,1,11,abc,,,,,,,,,,,
Hope the information provided is sufficient .
Thank you
gikjpjj
-
- Participant
- Posts: 39
- Joined: Tue Jun 03, 2008 12:13 am
- Location: Melbourne
Thank you brain .
Its giving the same problem.
I am still not able to resolve the problem.I am unable to insert NULL into a oracle stage.
When i am browsing all posts related to it , I found the below statement
Oracle Enterprise Stage issue (ecase 104805)
-----------------------------107791
Oracle Enterprise Stage aborts the job when trying to insert null into Oracle.
I came to know that its a bug in 7.5 version.
Can somebody tell me whether it is rectified in datastage 8.0.1 version ??
Thank you
Its giving the same problem.
I am still not able to resolve the problem.I am unable to insert NULL into a oracle stage.
When i am browsing all posts related to it , I found the below statement
Oracle Enterprise Stage issue (ecase 104805)
-----------------------------107791
Oracle Enterprise Stage aborts the job when trying to insert null into Oracle.
I came to know that its a bug in 7.5 version.
Can somebody tell me whether it is rectified in datastage 8.0.1 version ??
Thank you
gikjpjj
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Don't push. It's offensive.
DSXchange is an all-volunteer site whose members post as and when they can, if they have something to contribute. And they are under no compulsion to do so - unlike your official support provider whom you are paying to answer your questions.
DSXchange is an all-volunteer site whose members post as and when they can, if they have something to contribute. And they are under no compulsion to do so - unlike your official support provider whom you are paying to answer your questions.
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.