# SQL Queries using CTE and With

**URL:** <https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752>\
**Category:** Ignition\
**Created:** [February 15, 2024, 2:17pm UTC](https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752 "2024-02-15T14:17:55Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [February 15, 2024, 2:17pm UTC](https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752/1 "2024-02-15T14:17:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 15, 2024, 2:31pm UTC](https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752/2 "2024-02-15T14:31:38Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [February 15, 2024, 2:47pm UTC](https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752/3 "2024-02-15T14:47:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 15, 2024, 2:58pm UTC](https://forum.inductiveautomation.com/t/sql-queries-using-cte-and-with/85752/4 "2024-02-15T14:58:23Z")

</div>

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.