Data enrichment transform function for generating RRN for target table

[sundar_r]

Hi,

I am trying to set a data enrichment transform function(target_lookup) for generating RRN. My situation demands generating RRN since my source table will be truncated and reloaded everyday and target table should continue with RRN value max(RRN) + 1 of the target table.

I am trying to use the below data enrichment function but getting the mentioned error:

case when target_lookup(0,"AWSTEST","PAYHISTDAY","max('RRN')",'','')==NULL
then 1
else target_lookup(0,"AWSTEST","PAYHISTDAY","max('RRN')",'','')+1
end

Error:

Command failed to load data with exit error code 1, Command output: ERROR: null value in column "RRN" violates not-null constraint

Am I missing anything here, any help for resolving the error would be much appreciated.

Thanks,

Sundar Raman

[Heinvandenheuvel]

I think you have your quotes wrong.

While the target database (you fail to indicate which one) may like double quotes around object, the Replicate documentation clearly indicates using single quotes to pass object names and expressions as strings to it.

I would NOT quote the column name in the MAX function.  That's target SQL and normally does not need a quoted column name unless perhaps there is an uppercase/lowercase issue?

If it still fails, you should try to get the exact sql statement as requested either from the target database SQL trace, or from the reptask log in verbose mode. Of course you'd try this in DEV with a test table with a single, or no more than a handful of rows.

Finally I strongly encourage you NOT to use target_lookup but use a target DB computed value or trigger. Doing ( 2  per row) target lookups slows down Replicate a lot and is not even allowed on some (high  performance, CSV fed) DB's  such as Snowflake. Just do NOT replicate the RRN column and create a default value formula?

Good luck,

Hein.

[sundar_r]

Thanks Hein for the reply, my source table is DB2 and target is PostgreSQL. Looking at the verbose trace it looks like column name does require double quotes, removing them it throws an error. I tried creating a new target table with RRN column allowing null values and removing the primary key constraint, strangely it still throws the same error saying: null value in column "RRN" violates not-null constraint

[Heinvandenheuvel]

>> Looking at the verbose trace it looks like column name does require double quotes

Show us.

[john_wang · Accepted solution]

Hello @sundar_r , copy @Heinvandenheuvel ,

For NULL keyword the operator should be "is" rather than "==". Please use Replicate Expression Builder to make sure the expression is accurate.

Hope this helps.

Regards,

John.

[Lohore]

I wonder if your suggestions helped. As far as my knowledge goes, the issues described occur due to unoptimized functions in the table/CRM. Manual data enrichment is a time-consuming process that requires you to play detective, scouring company websites, Google, and individual LinkedIn and social media profiles. However, automated lead enrichment is a real salvation for businesses involved in the sales sector.

[john_wang]

Hello @Lohore ,

Please let us know the source/target DBs type and what's the exact usage, a usage sample is much helpful. We'd like to have more investigation for you.

Regards,

John.