-
1. Re: How to create table using with statement in the select statement of ddl
shawkins Oct 5, 2015 3:50 PM (in response to govindarajs)Can you show an example of what is not working for you?
-
2. Re: How to create table using with statement in the select statement of ddl
govindarajs Oct 6, 2015 8:27 AM (in response to shawkins)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.
-
3. Re: How to create table using with statement in the select statement of ddl
shawkins Oct 7, 2015 9:35 AM (in response to govindarajs)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 ....