Sample Bursting Query
This example of a bursting query is based on an invoice report. This report is to be delivered by CUSTOMER_ID to each customer's individual e-mail address
This example assumes that the delivery and formatting preferences for each customer are contained in a database table named "CUSTOMERS". The CUSTOMERS table includes the following columns that will be retrieved to create the delivery XML dynamically at runtime:
-
CST_TEMPLATE
-
CST_LOCALE
-
CST_FORMAT
-
CST_EMAIL_ADDRESS
The CUSTOMER_ID will be used as the KEY and also to define the output file name.
The SQL code to generate the delivery dataset for this example is as follows:
select distinct
CUSTOMER_ID as "KEY",
CST_TEMPLATE TEMPLATE,
CST_LOCALE LOCALE,
CST_FORMAT OUTPUT_FORMAT,
CUSTOMER_ID OUTPUT_NAME,
'EMAIL' DEL_CHANNEL,
CST_EMAIL_ADDRESS PARAMETER1,
'accounts.receivable@example.com' PARAMETER2,
'bip-collections@example.com' PARAMETER3,
'Your Invoices' PARAMETER4,
'Hi'||CUST_FIRST_NAME||':'|| 'Please find attached your
invoices.' PARAMETER5,
'true' PARAMETER6,
'donotreply@mycompany.com' PARAMETER7
from CUSTOMERS