Reg: NULL values in comma delimited file

[199542]

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).

[ray.wurlod]

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.

[199542]

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

Code: Select all

if  linkname.colname="" then setnull() else linkname.colname.

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

[ray.wurlod]

What is the data type of the YEAR field? My guess is Char(4).

Now, what is the Null Field Value property set to (a) for the YEAR field or (b) generically?

[199542]

Hi ray ,
Once thanks for your response.

In the metadata , I am specify just datatype.I am not specifying any length.Still i am able to view data.

Year char

Regarding the null field value , I am using for field YEAR.I am not using for all fields.

[ray.wurlod]

It is not possible to specify data type Char without a length (precision).

[devnull]

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).

Can you post your schema?

[devnull]

Can you post your schema?

I mean for the input stage.

[199542]

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

[Brian Zhang]

Hi,

Try this below,
Sequential File stage -> Output tab(Format tab) -> Null field value

And assign this property value that is rare to be met, such as &^%*($#@!~

And run your job again to see what will happen

Thanks
Brian

[199542]

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

[FDW_CITI]

Any answer for this issue.

[ray.wurlod]

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.

[FDW_CITI]

Sorry...

I didn't force.. Just wanted to know whether the issue was solved or not

[Nagaraj]

In Transformer give

In transformer give

if ISNULL(linkname.colname) then setnull() else linkname.colname

try this...!