Save sql results in a variable and use it another sql

Matillion ETL
1

I have a table from where I will be pulling dates and using those dates in where clause of another sql.

eg

query1

Test_var= select date from date_table

Query 2

select * from table where date=test_var

and append the data in a table

 

Loop until the end of results of query 1

 

 

 

 

 

2

Hi @kanika3010​

Store complete data from Query one in one table and use table iterator attached to transformation with Query two. Pass variable used in iterator through transformation scalar variable.

3

You can use a Query Result to Grid component to capture the results from the first query and use a Grid Iterator to iterate through the values passing to the transformation job. This way you can avoid creating another intermediate table.

4

Can u create a small template job and post. I am really a basic user and dont know much about it

5

Hi @kanika3010​

I will try to attach job template but meanwhile steps here:

You will have to create one job variable in both transformation and orchestration.

  1. 1st Transformation - which will run your Query 1 in SQL component and write it to a table using rewrite component.
  2. 2nd Transformation - Second query in SQL component with {variable_name} wherever required and write it to a table using rewrite component.
  3. Orchestration -
  • Add 1st transformation
    • Add table iterator component pointing to table created above
    • The same variable will be used in table iterator
    • Attach table iterator with 2nd Transformation
    • Add variable value to pass in 2nd transformation as scalar

I hope this helps.