sql - SQL编译错误: syntax error line 5 at position 157 unexpected ''

Question

这里可能出什么问题了？

create or replace temp table Whse_Role_Spend as
with 
Warehouse_Spend as (select sum(total_elapsed_time) Total_Elapsed, warehouse_name from 
"SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" group by warehouse_name),
Role_Spend as (select sum(total_elapsed_time) Total_Elapsed, warehouse_name, role_name from 
"SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" group by warehouse_name, role_name),
Credits_Used as (select sum(Credits_used) Credits_Used, warehouse_name from 
"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY" group by warehouse_name)

Answer 1

由于存在一个没有CTE查询的CTE定义，因此出现EOF错误。编译器在找到CTE查询之前先命中语句的末尾，因此它返回EOF错误。
CTE定义还需要在表表达式上定义列。您需要联接表，但是希望它们在CTE查询中(最后)。这将使您摆脱EOF问题，并接近可以按所需方式完成该语句的位置。

create or replace temp table Whse_Role_Spend as
with 
    Warehouse_Spend(total_elapsed, warehouse_name) as (select sum(total_elapsed_time) Total_Elapsed, warehouse_name from 
        "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" group by warehouse_name),
    Role_Spend (Total_Elapsed, warehouse_name, role_name) as (select sum(total_elapsed_time) Total_Elapsed, warehouse_name, role_name from 
        "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" group by warehouse_name, role_name),
    -- Add column definitions on the next table expression similar to the ones above.
    Credits_Used as (select sum(Credits_used) Credits_Used, warehouse_name from 
        "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY" group by warehouse_name)
select s.total_elapsed, 
       s.warehouse_name
from WAREHOUSE_SPEND S, ROLE_SPEND R; -- Join the CTE tables as you need them