New post
Question
· Jul 7, 2023

Alternatives to Common Table Expressions in IRIS

#SQL #InterSystems IRIS

Hi everybody,

I am writing an SQL query of the following form:

SELECT
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition2),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition3),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition4),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition5),
user.id
FROM UserTable user

where Condition1 depends on user and is the same for all four COUNT(*) subqueries. This query is very slow, and I believe that it can be sped up significantly if Condition1 would be checked once rather than four times. With a Common Table Expression, the query could select based on Condition1 once and then reuse the results:

WITH cte AS (
    SELECT id
    FROM DataTable
    WHERE Condition1
)
...

However, as far as I know, IRIS does not support Common Table Expressions. Is it possible to achieve similar results using the functionality available in IRIS? Thanks!

Product version: IRIS 2021.1
$ZV: IRIS for Windows (x86-64) 2021.1.3 (Build 389U) Wed Feb 15 2023 14:50:06 EST
Discussion (5)3
Log in or sign up to continue
Luis Angel Pére... · Jul 8, 2023

UserTable and DataTable are related? I'm guessing that if Condition1 is the relation between the tables you would try this query:

SELECT 
    COUNT(CASE WHEN data.a = "Condition1" then 1 ELSE NULL END) as "ValueA", 
    COUNT(CASE WHEN data.b = "Condition2" then 1 ELSE NULL END) as "ValueB", 
    COUNT(CASE WHEN data.c = "Condition3" then 1 ELSE NULL END) as "ValueC", 
user.id
 FROM UserTable user left join DataTable data on user.id = data.user
5 0
Benjamin De Boe · Jul 10, 2023

Hi, I don't think CTEs would help here, as you still should make sure to include the fields required for the Condition2-5.  I think @Luis Angel Pérez Ramos suggestion is the right way to go, using a JOIN and then CASE statements in the COUNT. If you can provide more details on the two tables and exact conditions, I'm sure we can help with the actual query you'll need.

CTEs are also mostly there for readability and wouldn't impact query performance by themselves. It's worth checking out if there aren't any opportunities for indices to speed up the JOIN and Condition1 parts.

All this said, we are planning to add CTE support to IRIS SQL in the near term.

2 0