DSXchange

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

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.

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

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?

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.

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

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

Can you post your schema?

Can you post your schema?

I mean for the input stage.

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

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

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

Any answer for this issue.

Sorry...

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

In Transformer give

In transformer give

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

try this...!