Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Store a SQL result in a variable

I want to store a SQL result in a variable. There doesn't appear to be an easy way to do this in Snowflake Matillion.

I have a batchid that I increment by one at the end of my load. I want to grab the current batchid and reference that in other parts of the job (eg. the load).

I want to SELECT max(batchid) from etl.batch_id and store the resulting 1 row of output in my $client_batchid variable.

Thanks,
Anthony

3 Community Answers

Anthony Schneider —

Found the answer to this myself (I think the documentation could be clearer). Use a Table Iterator and in the column mapping choose the column for the table or view with one result and map to variable.


Chris James —

That's been the easiest way I've found as well. I believe you can also export some values from a transformation to a parent job.


Matillion Agent  

Ian Funnell —

The Iterator method and the Export method both work, and you can also do this using a Python Script in Jython mode.

Open a cursor, fetch the value from the SQL query, and then use context.updateVariable to update the variable.

Best regards,
Ian

Post Your Community Answer

To add an answer please login