ParseException: SQL CTE

Question

result = aml_identity_g.connectedComponents()
conn_comps = result.select("id", "component",'type') \
  .createOrReplaceTempView("components") 
display(result)

Which creates

%sql
create table temptable 
as with dupes as (
   select component, count(case when type = 'Person' then 1 end) person_ct 
   from components 
   group by component 
   having person_ct > 1
)

Throws me an error as

Error in SQL statement: ParseException: 
mismatched input '<EOF>' expecting {'(', 'DESC', 'DESCRIBE', 'FROM', 'MAP', 'REDUCE', 'SELECT', 'TABLE', 'VALUES'}(line 6, pos 21)
   
== SQL ==
create table temptable 
as with dupes as (
    select component, count(case when type = 'Person' then 1 end) 
person_ct 
    from components 
    group by component 
    having person_ct > 1
)
---------------------^^^

Don't understand the error here.

Answer 1

You really don't need CTE in this query - it should be enough to have normal SELECT. So it should be something like this:

create table temptable as (
   select component, count(case when type = 'Person' then 1 end) person_ct 
   from components 
   group by component 
   having person_ct > 1
)

P.S. According to documentation on HAVING clause you can use alias in the HAVING expression.