# Handling of Common Table Expression

**URL:** <https://forum.confluent.io/t/handling-of-common-table-expression/787>\
**Category:** ksqlDB\
**Created:** [9 February 2021 12:27 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787 "2021-02-09T12:27:07Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![tchopra501](https://avatars.discourse-cdn.com/v4/letter/t/8e8cbc/32.png) [@tchopra501](https://forum.confluent.io/u/tchopra501)\
**Post date:** [9 February 2021 12:27 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/1 "2021-02-09T12:27:07Z")

</div>

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.

```sql
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

---

<div class="post-metadata">

**Author:** ![rmoff](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rmoff/32/3_2.png) [@rmoff](https://forum.confluent.io/u/rmoff)\
**Post date:** [9 February 2021 13:13 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/2 "2021-02-09T13:13:58Z")

</div>

You can’t do CTE in ksqlDB. You could raise an enhancement request at [Sign in to GitHub · GitHub](https://github.com/confluentinc/ksql/issues/new/choose) to suggest them though.

---

<div class="post-metadata">

**Author:** ![Charlla](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/charlla/32/243_2.png) [@Charlla](https://forum.confluent.io/u/Charlla)\
**Post date:** [9 February 2021 21:01 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/3 "2021-02-09T21:01:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tchopra501](https://avatars.discourse-cdn.com/v4/letter/t/8e8cbc/32.png) [@tchopra501](https://forum.confluent.io/u/tchopra501)\
**Post date:** [10 February 2021 09:37 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/4 "2021-02-10T09:37:11Z")

</div>

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.

```sql
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;
)

```

```sql
select * from cteSample;

```

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/chris/32/431_2.png) [@chris](https://forum.confluent.io/u/chris)\
**Post date:** [10 February 2021 22:10 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/5 "2021-02-10T22:10:44Z")

</div>

ksqlDB lets you create a persistent query from a select, will this work?

```auto
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.

---

<div class="post-metadata">

**Author:** ![rmoff](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rmoff/32/3_2.png) [@rmoff](https://forum.confluent.io/u/rmoff)\
**Post date:** [11 February 2021 11:06 UTC](https://forum.confluent.io/t/handling-of-common-table-expression/787/6 "2021-02-11T11:06:27Z")

</div>

A post was split to a new topic: [Can’t get UDF to show up in ksqlDB](https://forum.confluent.io/t/cant-get-udf-to-show-up-in-ksqldb/829)