CODEWITHSUNDEEP
etlinformaticainformatica-powercenterinformatica-cloud
Programmeur
Programmeur

Reputation: 190

Informatica cloud: use field in pre/post sql commands

I am trying to delete a set of data in the target table based on a column (year) from the lookup in IICS (Informatica Cloud).

I want to solve this problem using pre/post sql commands but the constraint is I can't pass year column to my query.

I tried this:

delete from sample_db.tbl_emp where emp_year = {year}

I want to delete all the employees in a specific year i get from lookup return

For Ex:

I got year as '2019', all the records in table sample_db.tbl_emp containing emp_year=2019 must be deleted.

I am not sure how this works in informatica cloud.

Any leads would be helpful.

Upvotes: 0

Views: 3311

Answers (2)

demircioglu
demircioglu

Reputation: 3465

The following steps would help you achieve this.

  1. Create an input-output parameter in your mapping.
  2. Assign the result of your lookup in an expression transformation to the parameter using SetMaxVariable
  3. Use the parameter in your target pre SQL as

delete from sample_db.tbl_emp where emp_year = $$parameter

Let me know if you have any further questions

Upvotes: 0

Scott S Nelson
Scott S Nelson

Reputation: 178

How are you getting the year value? A pre/post SQL may not be the way to go unless you need to do this as part of another transformation, i.e., before or after the transformation runs. Also, does your org only have ICDI, or also ICAI? ICAI may be a better solution depending on the value is being provided.

Upvotes: 0

Related Questions