how to lookup on a table based on a null key in informatica powercenter

Question

hello guys i have 2 tables

table 1
---------------
col1 col2 col3
NULL  1    update
123   1    y
159   2    f

table 2
----------------------
col1 col2 col3
NULL  1    x
123   1    z

i want my table 2 to be updated like this

table 2
---------------
col1 col2 col3
NULL  1    update
123   1    y
159   2    f

i tried 3 approaches and none of them worked for me first approach i tried treat source rows as: update in session properties and update else insert in table 2 options it worked in all rows except row 1 it inserted it instead of updating second approach i tried to use table 2 as a lookup and use update startegy to insert or update based on a table 1 cols to be null insert else update but it gave me the same output as the first approach does it treated row 1 as insert not update so i thought that the problem is it can use col1 in the join condition because of the null value

so i tried the 3rd approach: to drag table 2 as source and do expressions on col1 in the 2 tables the left join on table 2 and update startegy based on cols of table 1 if null insert else update but the problem here i can't pass the original columns to the update startegy as it's disallowed concatenation between active joiner transformation and source qualifier i only needed the expressions to have a successfull join and i was gonna use he original columns without the expressions so how can i solve something like that

Answer 1

The concatenation disallowed error basically means you can't combine the two flows due to a possible difference in row number or sort.

You need to use a joiner. At the same time you can't join on null.

My idea would be to: read the rows with null values, replace the null values in ports with something unlikely to see ever, e.g. -1, and insert those rows. Next, as a PostSQL statement I'd delete the old rows with nulls and update the -1 to null.

This is the general idea. You might need to work the details out.

Answer 2

Null cannot be considered as a key, update strategy will not allow you to update based on null values.

If you are tying to update, post update is the only option for you to update on the null key column.

It is advisable to have values which would consider as primary key if you are treating your row to update.

If you are trying to insert into a new table, you can use NVL functionality since your source is Oracle, define a default value when it is null. But to update any record, you must have a value.