Thanks Robin and Aniket for the suggestions you have given. Will try and update on the same.
Thanks, Elakiya On Fri, Oct 20, 2023 at 2:34 AM Robin Moffatt <ro...@decodable.co> wrote: > 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 >> >
