Non Nullable to Nullable using Modify stage

khanparwaz · Post by **khanparwaz** » Tue May 30, 2006 12:45 am

Hi,

I have a non nullable field in source. But in target this field is non nullable. I want to make it non nullable using modify stage. Currently this is achieved using transformer stage. Also if the value of the field in source is '' then i want to set it to null in target. I could find nullable to non nullable handling using handle_null functions but i want the reverse.
Can anyone help.

Thank In Advance.

ashwin141 · Post by **ashwin141** » Tue May 30, 2006 5:20 am

khanparwaz wrote:Hi,

I have a non nullable field in source. But in target this field is non nullable. I want to make it non nullable using modify stage. Currently this is achieved using transformer stage. Also if the value of the field in source is '' then i want to set it to null in target. I could find nullable to non nullable handling using handle_null functions but i want the reverse.
Can anyone help.

Thank In Advance.

Hi

You want to do non nullable to nullable or
nullable to non nullable?

Ashwin

kumar_s · Post by **kumar_s** » Tue May 30, 2006 6:14 am

Probably Missing out someting

If your target is not nullable, you cannot assign null to the column. If you input is not nullable, you cannot get null from the source.

khanparwaz · Post by **khanparwaz** » Tue May 30, 2006 11:22 pm

Hi,

My mistake. Source is Non-Nullable and Target is Nullable.

In the join stage if the join is outer and if the condition fails then the fields which are non nullable will have '' values.
In the target, if such values are nullable i want to assign Null to these fields.

eg If field1 = '' then SetNull else field1

But in the specification of Modify stage i can not use if then else...

Any idea how to achieve this...

Thanks in advance...

kumar_s · Post by **kumar_s** » Tue May 30, 2006 11:35 pm

What is the output stage. If it is sequential file stage, try to play with "Nullable field value" and "Default value", I guess you can acheive your need, using these option. And can avoid Modify stage as well. I dont have access right now, will let you now later.

benny.lbs · Post by **benny.lbs** » Wed May 31, 2006 1:07 am

khanparwaz,

Maybe you can try the following

After join stage, add Filter stage to filter into two links, one with '' values, the other without. Then add Modify stage after the link with '' values, and set the specification to be "field = make_null(field, "")". Then combine these two links with Funnel stage.

Is it what you wanted ?

ray.wurlod · Post by **ray.wurlod** » Wed May 31, 2006 1:28 am

What about simply targetfield:nullable integer=sourcefield ?

benny.lbs · Post by **benny.lbs** » Wed May 31, 2006 2:02 am

It seems his requirement not only to change the nullability, but also want to set NULL.

ray.wurlod wrote:What about simply targetfield:nullable integer=sourcefield ? ...

khanparwaz · Post by **khanparwaz** » Wed May 31, 2006 6:30 am

benny.lbs wrote:It seems his requirement not only to change the nullability, but also want to set NULL.

ray.wurlod wrote:What about simply targetfield:nullable integer=sourcefield ? ...

Yes I want to set the field to Null if its ''.

DSXchange