How to create table using with statement in the select statement of ddl

[govindarajs]

Hi,

     We are trying to create dynamic vdb using ddl files, in which we are trying to create table using with statement in ddl file

     but it is not working,

     Is it possible can i create table by using with statement in the select statement of ddl

Thanks & Regards

Govindaraj s.

[shawkins]

Can you show an example of what is not working for you?

[govindarajs]

Steven,

Please find the sample code as below

CREATE VIEW EMP

(

PK_EMP_ID VARCHAR(36) NOT NULL,

EMP_NAME VARCHAR(50) NULL,

EMP_DEPT_COUNT integer,

CONSTRAINT EMP_PK PRIMARY KEY (PK_EMP_ID)

)

AS

(WITH dept_count AS (

  SELECT deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT UUID(), e.ename AS employee_name,

       dc.dept_count AS emp_dept_count

FROM   emp e,

       dept_count dc

WHERE  e.deptno = dc.deptno;)

and while i am deploying this vdb, it returns the below error

ERROR TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered ") AS ( [*]WITH[*] dept_count AS" at line 52, column 4. Was expecting: "select" | "table" | "values" | "("

Thanks & Regards

Govindaraj s.

[shawkins]

The nesting parens are not required around the query that defines the view.  When they are there, the parser is expected a nestable query body rather than a full query expression that contain a with clause.  So instead don't use the top level parens:

AS WITH ....