Null issue in Date data type

[chrisjones]

Hi ,
I am trying to insert null in Date field in Oracle table which is nullable field, but somehow datastage or oracle is inserting '30-Dec-01' value instead of null.can any one suggest and help me how to keep in the null value in the date field.

[soumya5891]

Does the date field coming out from a join ?

[chrisjones]

Thanks for your reply..

it is coming from lookup...

[chulett]

Oracle supports a 'default value' but you need to not mention the column in your sql for it to be applied, if you specifically pass a null to the column it will be set to a null.

To me that means it is coming from your job, somewhere in there a default value is being applied.

[jyothisdasms]

I think your Look Up is not returning the null value when the join condition is not matching.Is the date field is coming from reference ?Check whether the nullability is set to 'Y' in look up for that field.

[soumya.buna]

check somewhere in column level properties default value has been set to the value which you are finding for the field.

[zulfi123786]

two possibilities:

1) your job is fetching the date mentioned instead of NULL from the look up

2) a default value for the column has been defined at the database lever

Replace the database stage and put a seq file stage and check what value datastage is fetching, is it a NULL or the date you mentioned.

If it is fetching a NULL then check if there is a dafault value specified at the table level.