Chaining Common Table Expressions in Impala

This article will help you to learn how to implement chaining common table expressions in Cloudera’s Impala.

-~ Chaining CTEs

WITH
Temp1 AS (
         SELECT OrderID,
                         OrderStatus,
                         ProductID
          FROM temp2
          WHERE order_status = ‘completed’
),
Temp2 AS (
          SELECT OrderID,
                          OrderStatus,
                          ProductID
          FROM Orders
          WHERE ProductID = 5
)
SELECT * FROM (SELECT COUNT(1) FROM Temp1) a;

Technically the query doesn’t make any sense, it can be written in a straight-forward and in a simple way –

SELECT COUNT(1)
FROM Orders
WHERE ProductID = 5
AND order_status=’completed’  

However, observe the structure or the pattern. In order to execute the first common table expression, it needs the values from the second expression.