How to avoid 'NO_DATA_TO_PROCESS' errors in BI Bursting schedule

KDR

This is actually possible to do in the Data Model SQL code directly. You can write your base query and then use a UNION to select the same number of fields as the base query with NULL values similar to the below:

i.e. UNION

SELECT NULL CHECK_DATE,
NULL PAYMENT_NUMBER, NULL CONTEXT_ID,
NULL TRANSACTION_ID
FROM DUAL

This guarantees that the query will fetch at least 1 (NULL) row of data to avoid the 'NO_DATA_TO_PROCESS' error.

Then in our Bursting query I just have logic in the WHERE clause to specify the CHECK_DATE column is between a certain range. If there is no other data to process from your main query then the NULL record from the UNION will be excluded here and no bursting output will occur.

Let me know if you want to see an exact example of this. Hope this helps!

Gianni Ceresa

Just being "picky" on @KDR solution: use a UNION ALL instead of UNION.

UNION will enforce a distinct constraint on the overall result to remove any possible duplicate, which is maybe not what you want. And even if your records are already unique and nothing will be lost, it does take quite some time based on how heavy is your query and the size of the dataset returned.