Unable to CREATE OR REPLACE VIEW with Databricks using CTE's

Question

I am trying to create a view in Databricks using the WITH method in Databricks with my SQL code.

However, I'm getting the error:

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
-----------------------------------------------------------------^^^

The full code is:

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

Any thoughts on why I'm unable to create a view?

Sample Data Set

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.

I hope the above sample data works

score 1 · Accepted Answer · answered Jul 04 '22 at 03:49

Try this instead , you can build a nested case statement rather than your implementation , however if your intention is towards a pivot structure do , let me know , I ll update the answere accordingly

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
;

Hi Vaebhav, thanks for reaching out. my intention was actual towards a pivot structure as you mentioned. Are you able to help with that? — Patterson, Jul 04 '22 at 07:05
Sure , add some sample data , I ll update the answer accordingly — Vaebhav, Jul 04 '22 at 08:26
@Patterson - Can you create the dataframe without the `insert` statement , a python implementation using `createDataframe` would be easier to work with — Vaebhav, Jul 04 '22 at 11:00

Unable to CREATE OR REPLACE VIEW with Databricks using CTE's

1 Answers1