Hi, how can i execute SQL script (select statement) and save results to a variable? Result should be scalar (so no multiple records or columns)? The problem is that SQL script needs to be dynamically passed, eg. I have SQL script that returns number of nulls in one column, but column name and table name are known in run time. These are the options i tried, but i wasn't successful. SQL script is stored in variable and passed as variable to components.
Hi Tanja,
I tried this SQL query: SELECT count("${column_name}") FROM db_name.schema_name.${target_table} and it worked in the component 'Query Result to Scalar'.
Then mapped the result of the query to a job variable for comparison.
Also, you can run the SELECT statement in the SQL transformation component, store the result in a view and pass it to the variable.
Please elaborate the question if I have not answered.
Regards,
Neelam
Hi,
I guessed that to be the issue but was not sure.
Please publish here if you find the solution.
Thanks,
Neelam
Hi @NeelamMacwan
Here is the update, how i implemented solution for above:
cursor = context.cursor()
#v_dsq is just variable that stores dynamic SQL, and following command executes SQL in redshift
cursor.execute(v_dsq)
rowcount = 0
# fetches the result of executed query and saves it in other variable
try:
rowcount = cursor.fetchone()[0]
if rowcount is not None:
context.updateVariable('j_dsq_result', rowcount)
else:
context.updateVariable('j_dsq_result', 0)
except e1:
context.updateVariable('j_dsq_result', rowcount)
print("Query returned no records - noting to be saved in variable j_dsq_result.\n")
Hi,
thank you for your reply.
Sorry, i realized ii haven't explained it properly. My script is passed dynamically but as a variable eg. ${v_sql_script}. The reason for this is that I'm loading different SQL files and query is not always same structured, so with every run i can have different SQL ready to be executed.
