Handling of Common Table Expression

ksqlDB
1

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

2

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

3

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.

1 Like
4

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;
5

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.

1 Like
6

A post was split to a new topic: Can’t get UDF to show up in ksqlDB