Databricks使用CTE创建视图触发ParseException错误

你写的建视图SQL存在基础语法错误：在CREATE OR REPLACE VIEW ... AS关键字后错误添加了分号;。
分号是SQL的单条语句终止标记，解析器读到这个多余的分号时，会直接判定建视图语句已经结束，但此时视图对应的查询逻辑完全没有定义，因此抛出mismatched input '<EOF>'的解析错误，提示缺少AS后应跟的查询内容。

只需要删除AS和WITH之间多余的分号即可正常运行：

CREATE OR REPLACE VIEW curorigination.opportunitiespresentationV3
AS
WITH numbering AS (
    SELECT CompanyOwner, CurrentOpportunityStatus, LastDateStatusChanged,
           rank() OVER (PARTITION BY CompanyOwner, CurrentOpportunityStatus ORDER BY LastDateStatusChanged DESC) AS rank
    FROM   enrorigination.opportunities_hv
)
SELECT CompanyOwner, 
       CASE CurrentOpportunityStatus WHEN 'Action - 1. Analysing'      THEN LastDateStatusChanged END AS `Action - 1. Analysing`,
       CASE CurrentOpportunityStatus WHEN 'Action - 2. Trying to meet' THEN LastDateStatusChanged END AS `Action - 2. Trying to meet`,
       CASE CurrentOpportunityStatus WHEN 'Action - 3. Date agreed'    THEN LastDateStatusChanged END AS `Action - 3. Date agreed`,    
       CASE CurrentOpportunityStatus WHEN 'Action - 4. Post meeting'   THEN LastDateStatusChanged END AS `Action - 4. Post meeting`,  
       CASE CurrentOpportunityStatus WHEN 'Action - 5. Chopped'        THEN LastDateStatusChanged END AS `Action - 5. Chopped`       
FROM  numbering
WHERE  rank = 1
ORDER BY CompanyOwner, CurrentOpportunityStatus, LastDateStatusChanged

• 用你提供的样例表temptable4测试时，只需要把CTE中FROM后的源表名替换为temptable4即可，行转列+分组取最新值的业务逻辑本身没有问题。
• Databricks中视图定义内的ORDER BY不保证查询视图时的返回顺序，若需要有序结果，建议在查询视图时额外添加ORDER BY子句，建视图阶段的排序在部分执行场景下会被优化器忽略。

内容的提问来源于stack exchange，提问作者Patterson