Alternative to WITH AS in SQL Queries

In this article, we’ll explore a common issue when working with Common Table Expressions (CTEs) and alternative solutions for achieving similar functionality without using the WITH keyword.

Background

Common Table Expressions are a powerful feature introduced in SQL Server 2005 that allow us to define temporary result sets by executing a query in the FROM clause. The CTE is then stored in a temporary result set, which can be referenced within the rest of the query. This allows for cleaner and more readable code, especially when working with complex queries.

However, some databases, including PostgreSQL and MySQL, do not support the WITH keyword. In these cases, we need to find alternative ways to achieve similar results.

One common issue is escaping the WITH keyword itself. The problem arises when trying to join a CTE with other tables. In this case, the query becomes ambiguous, as both the table and the CTE are being joined on the same column.

The original query

Let’s examine the original SQL query that sparked this discussion:

WITH table1 AS 
    (SELECT 
    tbl_.ticketsid 
    ,COUNT(CASE WHEN STATE= 3 THEN 1 ELSE NULL END) AS Closed
    ,COUNT(CASE WHEN STATE = 1 THEN 1 ELSE NULL END) AS Pending
FROM
    "tickets" "tbl_" 
    LEFT JOIN "fields" "tbl_f" ON "tbl_"."ticketsid" = "tbl_f"."ticketsid" 
    LEFT JOIN "taskinfo" "tbl_t" ON "tbl_"."ticketsid" = "tbl_t"."ticketsid"
    LEFT JOIN TaskDetails td ON td.TASKID=tbl_t.TASKID
    GROUP BY tbl_.ticketsid
    )

SELECT DISTINCT
    "tbl_"."ticketsid" AS "Ticket ID", 
    "tbl_"."TITLE" AS "Title", 
    CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE) OpenDate,
    "tbl_f"."UDF_CHAR13" AS "Category"
    ,t.Closed
    ,t.Pending
FROM
    "ticketsid" "tbl_" 
    FULL JOIN "fields" "tbl_f" ON "tbl_"."ticketsid" = "tbl_f"."ticketsid" 
    FULL JOIN "taskinfo" "tbl_t" ON "tbl_"."ticketsid" = "tbl_t"."ticketsid"
    FULL JOIN table1 t ON tbl_.ticketsid = t.ticketsid
WHERE
    TEMPLATEID = '123'

The Problem

As the original poster mentioned, they are unable to use the WITH keyword due to a report constraint. Instead of using WITH, we need to find an alternative way to achieve similar results.

Solution

One common solution for escaping the WITH keyword is to create a derived table by wrapping the CTE in another query:

SELECT DISTINCT
    "tbl_"."ticketsid" AS "Ticket ID", 
    "tbl_"."TITLE" AS "Title", 
    CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE) OpenDate,
    "tbl_f"."UDF_CHAR13" AS "Category"
    ,t.Closed
    ,t.Pending
FROM
    "ticketsid" "tbl_" 
    FULL JOIN "fields" "tbl_f" ON "tbl_"."ticketsid" = "tbl_f"."ticketsid" 
    FULL JOIN "taskinfo" "tbl_t" ON "tbl_"."ticketsid" = "tbl_t"."ticketsid"
    FULL JOIN (
        SELECT 
            tbl_.ticketsid 
            ,COUNT(CASE WHEN STATE= 3 THEN 1 ELSE NULL END) AS Closed
            ,COUNT(CASE WHEN STATE = 1 THEN 1 ELSE NULL END) AS Pending
        FROM
            "tickets" "tbl_" 
            LEFT JOIN "fields" "tbl_f" ON "tbl_"."ticketsid" = "tbl_f"."ticketsid" 
            LEFT JOIN "taskinfo" "tbl_t" ON "tbl_"."ticketsid" = "tbl_t"."ticketsid"
            LEFT JOIN TaskDetails td ON td.TASKID=tbl_t.TASKID
        GROUP BY tbl_.ticketsid
    ) t ON tbl_.ticketsid = t.ticketsid
WHERE
    TEMPLATEID = '123'

In this revised query, we’ve created a derived table by wrapping the original CTE in another SELECT statement. This allows us to avoid using the WITH keyword while still achieving similar results.

Alternative Solutions

As the original poster mentioned, there are alternative solutions for escaping the WITH keyword:

Using EXISTS: Instead of using DISTINCT, we can use EXISTS to check if a row exists in the derived table:

SELECT DISTINCT “tbl_”.“ticketsid” AS “Ticket ID”, “tbl_”.“TITLE” AS “Title”, CAST(DATEADD(SECOND, tbl_.OPENED/1000, ‘1970/1/1’) AS DATE) OpenDate, “tbl_f”.“UDF_CHAR13” AS “Category” FROM “ticketsid” “tbl_” FULL JOIN “fields” “tbl_f” ON “tbl_”.“ticketsid” = “tbl_f”.“ticketsid” FULL JOIN “taskinfo” “tbl_t” ON “tbl_”.“ticketsid” = “tbl_t”.“ticketsid” WHERE EXISTS ( SELECT 1 FROM table1 t WHERE t.ticketsid = tbl_.ticketsid AND TEMPLATEID = ‘123’ )

2.  **Using GROUP BY**: Instead of using `DISTINCT`, we can use a `GROUP BY` clause to group the results:
    ```sql
SELECT DISTINCT 
    "tbl_"."ticketsid" AS "Ticket ID", 
    "tbl_"."TITLE" AS "Title", 
    CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE) OpenDate,
    "tbl_f"."UDF_CHAR13" AS "Category"
FROM
    "ticketsid" "tbl_" 
    FULL JOIN "fields" "tbl_f" ON "tbl_"."ticketsid" = "tbl_f"."ticketsid" 
    FULL JOIN "taskinfo" "tbl_t" ON "tbl_"."ticketsid" = "tbl_t"."ticketsid"
GROUP BY "tbl_"."ticketsid", 
         "tbl_"."TITLE", 
         CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE), 
         "tbl_f"."UDF_CHAR13"

Using window functions: Instead of using a self-join, we can use window functions to calculate the counts:

SELECT DISTINCT “tbl_”.“ticketsid” AS “Ticket ID”, “tbl_”.“TITLE” AS “Title”, CAST(DATEADD(SECOND, tbl_.OPENED/1000, ‘1970/1/1’) AS DATE) OpenDate, “tbl_f”.“UDF_CHAR13” AS “Category” FROM ( SELECT “ticketsid” AS ticketsid, COUNT(CASE WHEN STATE= 3 THEN 1 ELSE NULL END) AS Closed, COUNT(CASE WHEN STATE = 1 THEN 1 ELSE NULL END) AS Pending, DATEADD(SECOND, OPENED/1000, ‘1970/1/1’) AS OpenDate FROM “tickets” “tbl_” GROUP BY ticketsid ) t INNER JOIN ( SELECT “ticketsid” AS ticketsid, COUNT(*) AS TotalCount FROM “tickets” “tbl_” WHERE TEMPLATEID = ‘123’ GROUP BY ticketsid ) c ON t.ticketsid = c.ticketsid

Conclusion
----------

In conclusion, while the `WITH` keyword is a powerful feature in SQL Server, it's not supported in all databases. By exploring alternative solutions such as creating a derived table, using EXISTS or GROUP BY clauses, or window functions, we can achieve similar results without relying on the `WITH` keyword.

Last modified on 2023-12-14