Project

General

Profile

Actions

Feature #17461

closed

Include WITH keyword

Added by Jan Haller 3 months ago. Updated about 1 month ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Target version:
Start date:
11.12.2023
Due date:
% Done:

0%

Estimated time:
Discuss:
Prio Planung:
No
Vote:

Description

The WITH keyword signifies a Common Table Expression (CTE). It allows you to refer to a subquery expression many times in a query, as if having a temporary table that only exists for the duration of a query. More
It has yet to be included in QFQ.


Files

Actions #1

Updated by Jan Haller 3 months ago

E.g.:
WITH RECURSIVE CountNumbers AS (
  SELECT 1 AS Number
  UNION ALL
  SELECT Number + 1
  FROM CountNumbers
  WHERE Number < 4
)

SELECT Number FROM CountNumbers;

Result:
Number
1
2
3
4
Actions #2

Updated by Jan Haller 3 months ago

WITH enables the possibility to fire subqueries as often as wanted without the need of actual records being selected.
E.g.:

10.sql = SELECT 10 AS _myCount
20 {
  sql = WITH RECURSIVE CountNumbers AS (
        SELECT 1 AS Number
        UNION ALL
        SELECT Number + 1
        FROM CountNumbers
        WHERE Number < '{{myCount:R0}}'
        )

        SELECT Number FROM CountNumbers

  30 {
    sql = SELECT '<br>'
  }
}

Actions #3

Updated by Carsten Rose about 2 months ago

  • Tracker changed from Support to Feature
Actions #4

Updated by Carsten Rose about 1 month ago

  • Target version changed from 24.1.2 to 24.1.0.rc1
Actions #5

Updated by Carsten Rose about 1 month ago

  • Status changed from New to Closed
Actions

Also available in: Atom PDF