CREATE TEMP TABLE FROM CTE

[Constantine]

I have written a CTE in Spark SQL

WITH temp_data AS (
 
......
 
)
 
CREATE VIEW AS temp_view FROM SELECT * FROM temp_view; 

I get a cryptic error. Is there a way to create a temp view from CTE using Spark SQL in databricks?

[-werners- · Accepted solution]

In the CTE you can't do a CREATE.

It expects an expression in the form of expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query )

where expression_name specifies a name for the common table expression.

If you want to create a view from a CTE, you can do this:

-- CTE in CREATE VIEW statement
CREATE VIEW v AS
    WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
    SELECT * FROM t;
SELECT * FROM v;
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|  1|  2|  3|  4|
+---+---+---+---+

PS. snippet from the Spark documentation

[jmorris]

Just want to say that you for replying to this. Just spent hours trying to figure out why my query wasn't working and this did the trick.