Hello all,
I’ve been working on some complex reporting challenges in Epicor, specifically with SQL queries that use advanced SQL functions like LAG() and OVER(). As many of you are aware, the BAQ designer’s parser struggles with these functions, which prevents us from using them directly in calculated fields.
I want to propose a concept that combines a few techniques discussed on this forum, particularly Kevin Lincrum’s “Dynamic Parameters” method and the use of the DynamicQuery object in a BPM. This isn’t a simple workaround; it’s a way to leverage the Epicor framework to achieve what the BAQ designer can’t do on its own.
The Problem
The BAQ designer is a powerful tool, but its parser is rigid; it often fails to validate calculated fields with complex clauses. This forces us to either write the query from scratch in a BPM or settle for less-efficient, multi-step BAQs.
The Proposed Solution: An Unofficial Hybrid Approach
I suggest a hybrid approach that combines the best of both worlds: the BAQ designer’s visual structure and the BPM’s ability to execute custom SQL.
- Use a mostly valid BAQ with a Placeholder: In the BAQ designer, create the main query with all the necessary tables and joins. For the complex calculated field (e.g.,
Run_ID), we would make a simple placeholder, like/* ApprovedPO */ null as Run_ID. The BAQ designer will validate this simple code without any errors.
-- This is the code generated by the BAQ designer based on your visual query.
-- The "NULL" is a simple placeholder that the BPM will later replace.
SELECT
POHeader.Company AS POHeader_Company,
PORel.Plant AS PORel_Plant,
PODetail.PartNum AS PODetail_PartNum,
POHeader.OrderDate AS POHeader_OrderDate,
POHeader.VendorNum AS POHeader_VendorVum,
POVendor.VendorID AS POHeader_VendorID,
-- ... other fields as needed
/* ApprovedPO */ NULL AS RunID,
FROM
Erp.POHeader
INNER JOIN Erp.PODetail ON ...
INNER JOIN Erp.PORel ON ...
INNER JOIN Erp.Vendor AS POVendor ON ...
WHERE
POHeader.Approve = 1
AND POHeader.Company = @CurrentCompany
AND POHeader.OrderDate >= @StartOfYear
AND PODetail.PartNum IN (@PartNum)
- Embed the Complex Logic in an Unused Subquery: The clever part is to create a separate subquery that is never joined to the main query . In this unreferenced subquery, we would add the complete, complex SQL code for our calculated field. The BAQ designer’s validation process doesn’t fully parse unlinked subqueries, which allows the advanced syntax to be saved without throwing an error.
- Use corresponding field names.
- Use the label to indicate the start marker of the table or placeholder.
-- This is the advanced SQL code you will put into the unused subquery.
-- It will be "injected" into the main query by the BPM at runtime.
SUM(CASE WHEN POHeader.VendorNum <> LAG(POHeader.VendorNum, 1, POHeader.VendorNum) OVER (
PARTITION BY POHeader.Company, PORel.Plant, PODetail.PartNum
ORDER BY POHeader.OrderDate
) THEN 1 ELSE 0 END) OVER (
PARTITION BY POHeader.Company, PORel.Plant, PODetail.PartNum
ORDER BY POHeader.OrderDate
ROWS UNBOUNDED PRECEDING
) AS Run_ID
- Use a BPM to replace the Code: The final step is to create a Pre-Processing BPM on the BAQ. This BPM would perform a find-and-replace operation on the BAQ’s generated SQL string at runtime. It would find our simple placeholder (
/* ApprovedPO */ null as Run_ID) and replace it with the complex, unvalidated SQL from our dummy subquery. Finally, the BPM would execute this new, complete SQL string using theDynamicQueryobject and then populate the BAQ’s result table with the data.
Why This Could Work
This approach is highly speculative and would require careful testing, but it seems possible in principle. It leverages the following:
- The BAQ designer’s validation blind spot for unreferenced subqueries.
- The BAQ’s ability to generate a valid SQL string based on a simple, validated structure.
- The BPM’s power to manipulate and execute a complete SQL string.
By using this method, we could build complex reports with the BAQ designer’s familiar interface while still incorporating advanced T-SQL that would otherwise be impossible.
Potential Risks and Considerations
While this method holds promise, it’s essential to be aware of the potential risks and limitations.
- Performance Overhead: The find-and-replace operation incurs additional overhead during query execution, particularly for very large or complex BAQ strings.
- Unofficial and Unsupported: This is an unorthodox, unofficial workaround. As a result, it is not supported by Epicor and could be impacted or broken by future software updates.
- Debugging Challenges: Debugging a BAQ that relies on a hidden subquery and a BPM can be very challenging. The BAQ designer’s trace will not show the final, modified SQL string, making it challenging to troubleshoot errors.
- Possible exploitation: As with all custom code, this has the potential of increasing the risk of exploitation by malicious actors.
What do you all think? Has anyone else tried something like this? It’s unorthodox, but it might be the key to unlocking more of SQL’s power within the Epicor environment.
Cheers,
Kevin
