Harriet_Kim_1764

How to E-mail To Recipients Based on SQL Query Output

Discussion created by Harriet_Kim_1764 on May 13, 2017
Objective:

A SQL query contains a USER_EMAIL column.
Based on the output, a user wants an automated process that will send e-mail to all the e-mail addresses listed in the USER_EMAIL column.

Solution:

1.  Create a VARA SQL (i.e. VARA.SQL.QUERY) and put the SQL query in there. 
2.  Create a SCRI or JOBS and put something like this in the Process tab: 

Example: 
:set &hnd# = prep_process_var(VARA.SQL.QUERY) 
:process &hnd# 
: set &email# = get_process_line(&hnd#,4) 
: set &email# = str_cat(&email#,";") 
: set &to_list# = str_cat(&to_list,&email#) 
:endprocess 
Where the USER_EMAIL is the 4th column in the example.
Ensure the right column number is being referenced in the get_process_line. 

Note: str_cat has a character limitation. 

3.  Use send_mail or &to_list# in a CALL object. 

Example: 
:SET &OUT# = SEND_MAIL("&to_list#",,"Meeting","Meeting is scheduled for Monday at 10AM.")

Outcomes