CODEWITHSUNDEEP
postgresqlamazon-redshiftcreate-table
Daniel
Daniel

Reputation: 93

Redshift - Create Table based on Nested Common Table Expressions

What is the syntax to create a new table based on results returned from "nested" common table expressions (example below)?

Example:

   WITH allCustomers as (SELECT Customerid FROM Customer_tbl),
    customer_purchasing as (SELECT Customerid, 
                                   Item, 
                                   Price 
                            FROM allCustomers 
                            JOIN purchases_tbl 
                              ON allCustomers.Customerid = purchases_tbl.Customerid
                               ) 
SELECT * FROM customer_purchasing

Question: How can I create a new table based on the customer_purchasing CTE in the example

Upvotes: 0

Views: 996

Answers (1)

Daniel
Daniel

Reputation: 93

Found the answer while writing my question: A table can be created based on nested CTE's by adding the following syntax to the example query.

CREATE TEMPORARY TABLE testtable1 as (
   WITH allCustomers as (SELECT Customerid FROM Customer_tbl),
    customer_purchasing as (SELECT Customerid, 
                                   Item, 
                                   Price 
                            FROM allCustomers 
                            JOIN purchases_tbl 
                              ON allCustomers.Customerid = purchases_tbl.Customerid)
SELECT * FROM customer_purchasing)

Upvotes: 1

Related Questions