Informatica cloud - Retrieve value from database and store in a variable

Question

I am working on Informatica cloud and need to use a value from the database in multiple transformations.The value will always be constant.How can I achieve this?Is there any way I can store it in a variable and then reuse it?

Answer 1

If you're using CDI, you can use an unconnected lookup transformation to define the object you want to query along with the return fields and lookup condition. Now define an in-out parameter to create a variable. Use an expression transformation and use a variable field to lookup this object using lookup function. In the field configuration, use the function

SetVariable($$NewParameter, :LKP.U_Lookup() )

Here $$NewParameter stands for the parameter you defined and U_Lookup is the name of the lookup transformation you defined. Now you can use the NewParameter in the downstream transformations as a variable.

If you're using CAI the you can define a temp field. You can use the DASBpelProcess available in this article to query a database object.Execute an SQL Statement in Cloud Application Integration

Use the DASBpelProcess as a subprocess in CAI process and assign the output from this subprocess to your temp variable as a formula using

$output.Response//*:row

Now you can use this temp_field as variable in the downstream process.