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.

AbulKhair

@Anjum H M Did you find a solution?

Vishal Purohit

Hi guys did you found the solution for this or trigger code ?

Abhishek Premchandra

The bursting sql can be enclosed in a trigger in the data model and in the Report -> Schedule -> Use Trigger has to be checked. When the scheduled report runs, and only when the trigger returns value, the report is run, otherwise it is 'Skipped', rather than 'Failed' which happens without using a trigger.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/76023#Comment_76023

There is no specific code for a scheduled trigger. It will be similar to your main query but will return true/false to indicate if the data will be returned by main query or not.

Check below links for working examples:

Skip Schedule using Schedule Triggers in BI Publisher

Creating Schedule Triggers

Thanks.