Search code examples
informatica-powercenter

How to pass sql script result value to variable in Informatica PowerCenter

I need to clear a storefront in teradata using data from an MSSQL table.

delete from table2 
where 1=1
   and version in (**values from MSSQL**)

enter image description here

Tried it in PowerCenter Designer create a variable using parameters and variables -> after trying to assign a value to it in an expression.

It didn't work out I continue to search for information, but have not yet found a similar topic, tell me how to implement something similar in computer science PorewCenter Maybe it can be done simpler, without binding to a variable

Perhaps this is possible using SQL conversion.

Solution

  • There are many ways to achieve it. Setting a variable in one mapping and using it in the following mappping is one. It works fine, but is tricky to set up because of the variable aggregation type. Show what you've done, where the error is and we'll assist.

    Another great way explanined by @Koushik. Clear and readable, might take long to execute depending on the amount of data to delete.

    Yet a different way would be to read the variable value from MS SQL and store it in Teradata, in some technical table, perhaps you can create one. And then just do a delete in second mapping's Pre-SQL like:

    delete from table2 
where 
version in (select version from config_table)