Common table expression

WITH [RECURSIVE] cte-table-name AS ( select-stmt )[,...] primary-select-stmt

What are Common Table Expressions?

Common table expressions ("CTEs") act like temporary views that exist only for the duration of a single ECSQL statement. There are two types of CTE:

Ordinary Common Table Expressions

This is mainly used to factor out subqueries, making the overall ECSQL statement easier to read and understand. It contains just a SELECT statement with or without RECURSIVE keyword.

  WITH
    el (Id, ClassId) AS (
      SELECT ECInstanceId, ECClassId FROM bis.Element
    ) SELECT * FROM el;

Recursive Common Table Expressions

A recursive common table expression can be used to walk a tree or graph. It is of the following form:

  cte-table-name AS ( initial-select) UNION [ALL] recursive-select)

Here is a simple example of how we can write a CTE. In the following query we want to generate a sequence from 1 through 5. We start with an initial value of x = 1 and then recursively do x+1 until the value of x is less then 6.

  WITH RECURSIVE
    cnt (x) AS (
        SELECT 1
        UNION ALL
        SELECT x+1 FROM cnt WHERE x<6
    )
  SELECT * from cnt;

-- output
  x
  ------
  1
  2
  3
  4
  5
  6

As another example, we might want to traverse a class hierarchy starting from a base class down to all derived classes, generating a row for each class. Each row should could contain 2 columns: the depth of the derived class relative to the base class and a path string describing its relationship to the base class. Using BisCore:GeometricElement2d as the base class produces the following ECSQL and resultant output:

WITH RECURSIVE
      base_classes (aId, aParentId, aPath, aDepth) AS (
          SELECT c.ECInstanceId, NULL, c.Name, 0  FROM meta.ECClassDef c WHERE c.Name='GeometricElement2d'
          UNION ALL
          SELECT c.ECInstanceId, cbc.TargetECInstanceId, aPath || '/' || c.Name, aDepth + 1
              FROM meta.ECClassDef c
                  JOIN meta.ClassHasBaseClasses cbc ON cbc.SourceECInstanceId = c.ECInstanceId
                  JOIN base_classes  ON aId = cbc.TargetECInstanceId
      )
  SELECT bc.aDepth depth, bc.aPath FROM base_classes bc
    JOIN meta.ECClassDef a ON a.ECInstanceId= bc.aId
    JOIN meta.ECClassDef b ON b.ECInstanceId= bc.aParentId;;

-- output
depth | aPath
---------------------------------------
1     | GeometricElement2d/GraphicalElement2d
2     | GeometricElement2d/GraphicalElement2d/AnnotationElement2d
2     | GeometricElement2d/GraphicalElement2d/DrawingGraphic
2     | GeometricElement2d/GraphicalElement2d/ViewAttachment
2     | GeometricElement2d/GraphicalElement2d/DetailingSymbol
3     | GeometricElement2d/GraphicalElement2d/AnnotationElement2d/TextAnnotation2d
3     | GeometricElement2d/GraphicalElement2d/DrawingGraphic/SheetBorder
3     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/Callout
3     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/TitleText
3     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/ViewAttachmentLabel
4     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/Callout/DetailCallout
4     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/Callout/ElevationCallout
4     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/Callout/PlanCallout
4     | GeometricElement2d/GraphicalElement2d/DetailingSymbol/Callout/SectionCalloutt