Why non-nullable record is rejected after lookup failed?

[Yuan]

Hi, I got a problem about lookup stage

I have a non-nullable column(varchar) from input like Lnk_input.Column1, and updated its value using lookup stage. The output is something like Lnk_output.Column1. The lookup stage is set to "continue after lookup failed".

According to the DataStage document, Lnk_output.Column1 will be assigned a default value if lookup failed. And I'm expecting that "default value" to be the same as input link(Lnk_input.Column1). So the logic I'm trying to implement is: if lookup success, use the new value; else use the original one. But, the record is just rejected silently.

Does someone has some idea? I know this could be implemented using Transform stage. But I'm just wondering if there is a "build-in" way in lookup stage for this. Thanks!

[ray.wurlod]

If your lookup fails and the Lookup Failed rule is Continue then every column from the reference input is set to NULL. You are not permitted to send NULL into an output field that it not nullable. Therefore, failure.

Inspect the output fields' extended properties. Do you have Default property defined for every one of them?

[Yuan]

But I found no place in the loopup stage to set a default value for the column. The extended property is shown as "None available"

[priyadarshikunal]

But I found no place in the loopup stage to set a default value for the column. The extended property is shown as "None available"

you want a piece of cake ready to eat!

Just pass the stream link attribute along with the value getting from reference link after making it nullable.

and in down stream transformer check if the value from reference link is null then pass the value of stream link.

Regards,

[Yuan]

But I found no place in the loopup stage to set a default value for the column. The extended property is shown as "None available"

you want a piece of cake ready to eat!

Just pass the stream link attribute along with the value getting from reference link after making it nullable.

and in down stream transformer check if the value from reference link is null then pass the value of stream link.

Regards,

Yeah, I know this gonna to work. I just want to know whether there's a way to avoid this kind of manually null-checking and what's the "default value" in the document means. Now I guess maybe this is the only way to resolve then.

[priyadarshikunal]

But I found no place in the loopup stage to set a default value for the column. The extended property is shown as "None available"

you want a piece of cake ready to eat!

Just pass the stream link attribute along with the value getting from reference link after making it nullable.

and in down stream transformer check if the value from reference link is null then pass the value of stream link.

Regards,

Yeah, I know this gonna to work. I just want to know whether there's a way to avoid this kind of manually null-checking and what's the "default value" in the document means. Now I guess maybe this is the only way to resolve then.

Here default value is null thats why you can't pass it to non nullable column in lookup stage with lookup failure mode as continue.

One more way is to set lookup failure mode as reject and then funnel reject link output with stream link output via copy stage where you need to change the column name to synchronize it with stream link metadata.

Regards,