SQL Queries using CTE and With

Ignition
1

The last several gateways I worked on, I was not able to use CTE or With in a named query.

Did an update enable using those or was there a setting all along to use them?

2

The WITH keyword confuses the auto-detection of update versus query mode. Recent versions allow you to force one mode or the other. (It's been quite a while, I think.)

2 Likes
3

Do you use them or avoid using CTE and With keywords in the named queries?

I have multiple queries where I use the same base query 4 times.

Usually I get main range I need.
Then I get the edge case.
Usually I reuse both subqueries again to union total to the bottom row.

4

I use CTEs when I need them. There are two such situations:

  • I need recursion that can only be expressed with a CTE, or

  • I need an identical subquery in two (or more) places in an outer query--the duplicate subquery is then factored into a CTE for performance.

I don't use CTEs as an alternate syntax for general subqueries.

2 Likes