insert and updateConstraints with columns having null values

[Sandeepch19]

I am having a constraint at the transformer level for insert else update, we are comparing 9 column all of them are nullable fields, using these 9 fields we are either updating or inserting the records,
My job is as below

Code: Select all

                    Lookup
                   |
                   |
                   v
Source--->transformer---->insert the records(OCI stage)
                  |
                   --------------->update  the records(OCI stage)

If the records is there in lookup then we just should update 3 column otherwise we should insert

insert constraint is like this --isnull(col1)and isnull(col2) ad isnull(col3).....isnull(col9)

Update constraint is like this --not(isnull(col1) and not(isnull(col2) ad not(isnull(col3)) ...... not(isnull(col9))

but many of the columns are having null values so the values will not be compared because of this the records are not getting updated. Can anyone let meknow how to handle this at datastage level.

[chulett]

All you need to check for null is one of the Key columns. That or use the NOTFOUND link variable if this is a hashed file lookup.

[Sandeepch19]

All you need to check for null is one of the Key columns. That or use the NOTFOUND link variable if this is a hashed file lookup. ...

My qurey is as below, i hope this is not ambigous
I have 9 columns as lookup fields(key columns but it is nullable) out of which 4 are coming as null. if we are having null values in lookup columns then lookup will not work. I am not getting the desired result because of this.

[ray.wurlod]

Tough. You simply cannot look up NULL. It is not a known value. (That's what NULL signifies.)

Here's an analogy: I have something in my hand. Tell me what it is. (That's the equivalent of a lookup - you have no way of knowing what I have in my hand.)

[Sandeepch19]

Can i do it this way, while comparing(look up) replace null with some value on both the sides(both source and lookup), so that it matches the constraint and the records will flow to the target.

[chulett]

Yes, that would be the typical solution to this particular problem, the use of 'in-band' nulls on both sides of the lookup.

[Sandeepch19]

Yes, that would be the typical solution to this particular problem, the use of 'in-band' nulls on both sides of the lookup. ...

Could yo please let me know which function we can use to resolve this particular issue. While comparing we need to change the null value into some defualt value by doing this the data will match and will flow to the target accroding to the logic but while loading it should be null.

[chulett]

Nothing magical, simply check for null and if found, set to your "in-band" value otherwise keep the original value. On the way back out, reverse the check. Biggest thing is to make sure the value you pick will never occur naturally in your data.

For the first check, you have the NullToValue() transform you could leverage but the reverse does not exist. Doesn't mean you couldn't write one, however.