Hi , I am trying Common Table Expression(CTE) using below example but its not working , can you please tell how to implement CTE in KSQL where I want to select from table or topic. WITH cteSample (VBELN, MANDT, POSNR) AS ( SELECT lips.VBELN,lips.MANDT,lips.POSNR FROM JSON_LIMITED_2_LIPS_S1 lips LEFT JOIN JSON_LIMITED_2_LIKP_S1 likp WITHIN 7 DAYS ON lips.VBELN = likp.VBELN LEFT JOIN JSON_LIMITED_2_VBBE_S1 vbbe WITHIN 7 DAYS ON vbbe.VBELN = likp.VBELN EMIT CHANGES LIMIT 1; ) Regards, Tarun

You can’t do CTE in ksqlDB. You could raise an enhancement request at Sign in to GitHub · GitHub to suggest them though.

Interresting, what do you need a CTE for? Are you after recursion, or something like row_num? I have not given recursion much thought in ksqldb, a sample use case would be cool. For other uses, it would be challenging as normally a cte is bound to perform grouping functions. You could probably get very close to the same behavior with aggregates or a udf? In such a case, limit would be your bound and the aggregation could start at an arbitrary value which could mimic something like row_number. For n_tile like functions or ranks, you probably need a udf or aggregate udf.

I have already provided use case , with CTE we can then reference the CTEs as we can refer any other table , not sure using aggregate functions or using UDF how it can be implemented , will appreciate if you share sample code how to implement this or some example. WITH cteSample (VBELN, MANDT, POSNR) AS ( SELECT lips.VBELN,lips.MANDT,lips.POSNR FROM JSON_LIMITED_2_LIPS_S1 lips LEFT JOIN JSON_LIMITED_2_LIKP_S1 likp WITHIN 7 DAYS ON lips.VBELN = likp.VBELN LEFT JOIN JSON_LIMITED_2_VBBE_S1 vbbe WITHIN 7 DAYS ON vbbe.VBELN = likp.VBELN EMIT CHANGES LIMIT 1; ) select * from cteSample;

ksqlDB lets you create a persistent query from a select, will this work? CREATE STREAM cteSample as SELECT lips.VBELN,lips.MANDT,lips.POSNR FROM JSON_LIMITED_2_LIPS_S1 lips LEFT JOIN JSON_LIMITED_2_LIKP_S1 likp WITHIN 7 DAYS ON lips.VBELN = likp.VBELN LEFT JOIN JSON_LIMITED_2_VBBE_S1 vbbe WITHIN 7 DAYS ON vbbe.VBELN = likp.VBELN EMIT CHANGES; This will register the stream cteSample(and also create a Kafka topic of the same name) allowing you to then run SELECT * FROM cteSample EMIT CHANGES; or directly consume it as a topic.

Handling of Common Table Expression

Stream Processing ksqlDB

Topic		Replies	Views
Create Table without data aggregation ksqlDB	1	4151	17 March 2022
Handling of Windowing function in KSQL like ROW_NUMMBER() , RANK() and DENSE_RANK() ksqlDB	8	4742	16 July 2021
Equivalent of SQL Union in KSQLDB ksqlDB	3	5140	14 September 2022
RECORDING AVAILABLE - ksqlDB 101 Events	0	3295	19 November 2021
Data Snapshot using KSQL ksqlDB	2	3217	28 October 2021

Handling of Common Table Expression

Related topics