Support for Common Table Expressions (CTEs) in Panther DBi

Panther's Database Interface (DBi) add-ons now support SQL statements that use Common Table Expressions (CTEs) via the WITH clause. This enhancement allows Panther to bind result sets from DBMS SQL WITH statements to fields and variables, just as it does with standard DBMS SQL SELECT statements.

Previously, Panther could execute WITH statements, but could not bind their result sets, limiting their practical use. With this update, developers can now use CTEs to structure complex queries while maintaining full data binding support.

Note: Chained WITH clauses, where multiple CTEs are defined in sequence, are not supported. Only a single WITH clause may be used per statement.

Example (Oracle-Compatible JPL)

DBMS SQL WITH Sales_CTE AS ( \
    SELECT  \
        sales_amount, \
        SUM(sales_amount) OVER () AS total_sales \
    FROM \
        sales \
) \
SELECT \
    sales_amount AS amount, \
    total_sales \
FROM \
    Sales_CTE

This query defines a temporary result set Sales_CTE that computes total_sales using Oracle's analytic SUM(... OVER ()) function. Panther binds the amount and total_sales columns to fields or variables as with any standard DBMS SQL SELECT statement.