无法使用CTE的数据库创建或替换视图

Question

我试图用SQL代码在Databricks中使用WITH方法在Databricks中创建一个视图。

但是，我得到了一个错误：

Error in SQL statement: ParseException: 
mismatched input '<EOF>' expecting {'(', 'UP_TO_DATE', 'AS', 'COMMENT', 'PARTITIONED', 'TBLPROPERTIES'}(line 1, pos 65)

== SQL ==
CREATE OR REPLACE VIEW curorigination.opportunitiespresentationV3
-----------------------------------------------------------------^^^

完整的代码是：

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

对我为什么不能创建视图有什么想法吗？

样本数据集

CREATE TABLE temptable4 (
     CurrentOpportunityStatus nvarchar(50),
     LastDateStatusChanged date,
     CompanyOwner nvarchar(50),
     OpportunityDescription nvarchar(max),
     CreatedOn datetime2,
     OpportunityName nvarchar(100))
    
 INSERT temptable4 VALUES
 (N'Tip - Anticipated',CONVERT(DATETIME, '2022-03-11', 120),N'',N'cc',CONVERT(DATETIME2, '2022-03-11 09:17:02.0000000', 121),N'CC ''22'),
 (N'Deal - Won',CONVERT(DATETIME, '2022-03-10', 120),N'Edward Shuckburgh',N'Flagged by Frank L in Frankfurt. CDK is the leading provider of Dealer Management Systems (DMS) to the automotive industry globally. Frank recently met with Patrick Katenkamp (former CEO of Incadea, a competitor to CDK) who mentioned that CDK may be open to carving-out their UK business. CDK is predominantly US focussed and is understood to be considering what to do with its international businesses. Unclear on size of the UK operation, but it may be worth reaching out to better understand the company''s situation. DM first met Neil Packham via an intro from HIEC in Feb 2020.',CONVERT(DATETIME2, '2018-05-24 10:55:00.0000000', 121),N'CDKI ''18'),
 (N'Deal - WIP',CONVERT(DATETIME, '2022-03-08', 120),N'Sophie Hoas',N'Allegedly at 40mn USD business providing on-line training for the US bar and UK professional legal exams. Better positioned to cope with the proposed changes in training regime in the UK.',CONVERT(DATETIME2, '2020-11-24 12:48:53.0000000', 121),N'barbri ''20'),
 (N'Deal - Won',CONVERT(DATETIME, '2022-03-08', 120),N'Nicole Dixson',N'G3 / Good Governance Group is a strategic advisory consultancy which specialises in providing advice on risk mitigation, governance, cyber security and regulatory compliance. Described as a \"mini Hakluyt\" and doing £12-15m EBITDA. Reputational concerns about clients, and likely people heavy - chop.',CONVERT(DATETIME2, '2021-11-05 08:47:16.0000000', 121),N'G3 ''21'),
 (N'Deal - Won',CONVERT(DATETIME, '2022-03-04', 120),N'Christoph Leitner-Dietmaier',N'Battery-backed roll-up of ERP businesses.  They have now changed management (based in the UK, former CEO and CFO of CoreHR) and started to unify previously disparate operations.  €150m revenues and €40m EBITDA. Something could happen over next 6-12 months according to Nomura
 25-Oct-21 CLD: Arma mandated, Paul G mentioned that they will intro 5 parties to management pre-Xmas for process launch in the NY.',CONVERT(DATETIME2, '2021-08-03 13:33:58.0000000', 121),N'Forterro ''21'),
 (N'Tip - Anticipated',CONVERT(DATETIME, '2022-03-04', 120),N'',N'Test Opportunity',CONVERT(DATETIME2, '2022-03-04 12:35:53.0000000', 121),N'19 Entertainment ''22'),
 (N'Tip - Anticipated',CONVERT(DATETIME, '2022-03-04', 120),N'',N'test',CONVERT(DATETIME2, '2022-03-04 12:40:23.0000000', 121),N'123.ie ''22'),
 (N'Deal - Won',CONVERT(DATETIME, '2022-03-03', 120),N'Geeta Hirani',N'Bryan Garnier mandate \"Project Goldeneye\", this is a specialist diagnosic microscope scanner business based in Finland. Allegedly making €9m EBITDA (up from 1), growing strongly. To look into. Tip from DM.',CONVERT(DATETIME2, '2021-12-16 11:12:06.0000000', 121),N'Grundium ''21'),
 (N'Deal - WIP',CONVERT(DATETIME, '2022-03-02', 120),N'James Cann',N'Description',CONVERT(DATETIME2, '2022-03-02 10:31:52.0000000', 121),N'JC training'),
 (N'Action - 1. Analysing',CONVERT(DATETIME, '2022-02-07', 120),N'Geeta Hirani',N'Market-leading Nordic HR tech platform focused primarily on recruitment (c.80% of ARR), onboarding and talent management. Pure-play SaaS business with c. €20m of ARR. Created by a merger of three smaller companies by Verdane who invested in 2018. Houlihan Lokey (GCA Altium) has been mandated to run a narrow sales process as Verdane can no longer fund further M&A due to fund concentration limits. We know the CEO from Visma where he was in the Finance Director of the Enterprise Division. Potential target for EMM.',CONVERT(DATETIME2, '2021-12-15 11:25:53.0000000', 121),N'Talentech ''21'),
 (N'Action - 5. Chopped',CONVERT(DATETIME, '2022-02-02', 120),N'Catherine Parry',N'hhh',CONVERT(DATETIME2, '2021-12-21 12:23:41.0000000', 121),N'JC Opp test 2'),
 (N'Tip - Anticipated',CONVERT(DATETIME, '2022-02-02', 120),N'',N'Test Opportunity for new EMM product',CONVERT(DATETIME2, '2022-02-02 16:28:05.0000000', 121),N'Test Opportunity ''22'),
 (N'Tip - Anticipated',CONVERT(DATETIME, '2022-02-02', 120),N'',N'test opportunity',CONVERT(DATETIME2, '2022-02-02 17:16:23.0000000', 121),N'New Test Opportunity ''22'),
 (N'Deal - Won',CONVERT(DATETIME, '2021-12-21', 120),N'James Cann',N'Opp',CONVERT(DATETIME2, '2021-12-21 11:18:07.0000000', 121),N'JC Opp Reg Test'),
 (N'Action - 5. Chopped',CONVERT(DATETIME, '2021-12-16', 120),N'Frank Löhner',N'With our fully digitized RegTech and Capital Markets Tech solutions, our state-of-the-art technologies and in-depth knowledge of capital markets we develop your digital future.

我希望上面的样本数据能正常工作。

Answer 1

尝试这样做，您可以构建一个嵌套的case语句，而不是您的实现，但是如果您的意图是实现一个枢轴结构，请让我知道，我将相应地更新answere。

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
         WHEN CurrentOpportunityStatus = 'Action - 1. Analysing'      THEN LastDateStatusChanged 
         WHEN CurrentOpportunityStatus = 'Action - 2. Trying to meet' THEN LastDateStatusChanged 
         WHEN CurrentOpportunityStatus = 'Action - 3. Date agreed'    THEN LastDateStatusChanged 
         WHEN CurrentOpportunityStatus = 'Action - 4. Post meeting'   THEN LastDateStatusChanged 
         WHEN CurrentOpportunityStatus = 'Action - 5. Chopped'        THEN LastDateStatusChanged 
       END AS CurrentOpportunityStatus,
      LastDateStatusChanged
FROM  numbering
WHERE  rank = 1
ORDER BY CompanyOwner, CurrentOpportunityStatus, LastDateStatusChanged
;