CTEs are supported, you can see an example in the docs [1] [2]. In the latter doc, it also says
> CTEs are supported in Views, CTAS and INSERT statement So I'm just guessing here, but your SQL doesn't look right. The CTE needs to return a column called `pod`, and the `FROM` clause for the `SELECT` should be after it, not before the `INSERT`. i.e. something like this instead: WITH p1 AS ( SELECT empId AS pod FROM employee ) INSERT INTO correlate SELECT pod FROM p1; Hope that helps, Robin [1]: https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/sql/queries/with/ [2]: https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/hive-compatibility/hive-dialect/queries/cte/ On Thu, 19 Oct 2023 at 08:05, elakiya udhayanan <laks....@gmail.com> wrote: > Hi Team, > > I have a Flink job which uses the upsert-kafka connector to consume the > events from two different Kafka topics (confluent avro serialized) and > write them to two different tables (in Flink's memory using the Flink's SQL > DDL statements). > > I want to correlate them using the SQL join statements and for this I am > trying to use the cte expressions like below (sample): But getting > exception as below > > *org.apache.flink.table.api.SqlParserException: SQL parse failed. > Incorrect syntax near the keyword 'INSERT'* > > WITH p1 AS ( SELECT empId FROM employee ) > FROM p1 > INSERT INTO correlate > SELECT pod; > > Please let me know if queries with cte are supported in Apache Flink. > > Thanks, > Elakiya >
