Showing posts with label chaining common table expressions in impala; multiple CTEs in a query; parent child common table expressions; multiple CTEs;. Show all posts
Showing posts with label chaining common table expressions in impala; multiple CTEs in a query; parent child common table expressions; multiple CTEs;. Show all posts

Wednesday, September 30, 2020

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. 

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...