QUALIFY to derived table
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Teradata introduced the useful QUALIFY clause, which has since been reproduced by a number of implementations. jOOQ can transform a query containing the QUALIFY clause to an equivalent query filtering on a derived table
This transformation allows for transforming some of these syntaxes to equivalent, standard syntax using window functions and derived tables:
-- Input SELECT * FROM t QUALIFY row_number () OVER (ORDER BY id) <= 5
-- Output SELECT * FROM ( SELECT *, row_number () OVER (ORDER BY id) AS rn FROM t ) AS t WHERE t.rn <= 5
Example configuration
Settings settings = new Settings()
.withTransformQualify(Transformation.WHEN_NEEDED);
Feedback
Do you have any feedback about this page? We'd love to hear it!