Skip to content

    Query Language (CQL)

    CDS Query Language (CQL) is based on standard SQL, which it enhances by…

    Postfix Projections

    CQL allows to put projections, that means, the SELECT clause, behind the FROM clause enclosed in curly braces. For example, the following are equivalent:

    SELECT name, address.street from Authors
    SELECT from Authors { name, address.street }

    Smart * Selector

    Within postfix projections, the * operator queries are handled slightly different than in plain SQL select clauses.


    SELECT from Books { *, as author }

    Queries like in our example, would result in duplicate element effects for author in SQL. In CQL, explicitly defined columns following an * replace equally named columns that have been inferred before.

    Excluding Clause

    Use the excluding clause in combination with SELECT * to select all elements except for the ones listed in the exclude list.

    SELECT from Books { * } excluding { author }

    The effect is about late materialization of signatures and staying open to late extensions. For example, assume the following definitions:

    entity Foo { foo : String; bar : String; car : String; }
    entity Bar as SELECT from Foo excluding { bar };
    entity Boo as SELECT from Foo { foo, car };

    A SELECT * from Bar would result into the same as a query of Boo:

    SELECT * from Bar --> { foo, car }
    SELECT * from Boo --> { foo, car }

    Now, assume a consumer of that package extends the definitions as follows:

    extend Foo with { boo : String; }

    With that, queries on Bar and Boo would return different results:

    SELECT * from Bar --> { foo, car, boo }
    SELECT * from Boo --> { foo, car }

    Path Expressions

    Use path expressions to navigate along associations and/or struct elements in any of the SQL clauses as follows:

    • In from clauses:
      SELECT from Authors[name='Emily Brontë'].books;
      SELECT from Books:authors.towns;
    • In select clauses:
      SELECT title, from Books;
      SELECT *, from Books;
    • In where clauses:
      SELECT from Books where'Emily Brontë'
    • The same is valid for group by, having, and order by.

    Path Expressions in from Clauses

    Path expressions in from clauses allow to fetch only those entries from a target entity, which are associated to a parent entity. They unfold to SEMI JOINS in plain SQL queries. For example, the previous mentioned queries would unfold to the following plain SQL counterparts:

    SELECT * from Books WHERE EXISTS (
      SELECT 1 from Authors WHERE Authors.ID = Books.author_ID
        AND'Emily Brontë'
    SELECT * from Towns WHERE EXISTS (
      SELECT 1 from Authors WHERE Authors.town_ID = Towns.ID AND EXISTS (
        SELECT 1 from Books WHERE Books.author_ID = Authors.ID

    Path Expressions in All Other Clauses

    Path expressions in all other clauses are very much like standard SQL’s column expressions with table aliases as single prefixes. CQL essentially extends the standard behavior to paths with multiple prefixes, each resolving to a table alias from a corresponding LEFT OUTER JOIN. For example, the path expressions in the previous mentioned queries would unfold to the following plain SQL queries:

    -- plain SQL
    SELECT Books.title, from Books
    LEFT JOIN Authors author ON author.ID = Books.author_ID;
    -- plain SQL
    SELECT Books.*, from Books
    LEFT JOIN Authors author ON author.ID = Books.author_ID
    LEFT JOIN Addresses author_address ON author_address.ID = author.address_ID
    LEFT JOIN Towns author_address_town ON author_address_town.ID = author_address.town_ID;
    -- plain SQL
    SELECT Books.* from Books
    LEFT JOIN Authors author ON author.ID = Books.author_ID
    WHERE'Emily Brontë'

    All column references get qualified → in contrast to plain SQL joins there’s no risk of ambiguous or conflicting column names.

    With Infix Filters

    Append infix filters to associations in path expressions to narrow the resulting joins. For example:

    SELECT books[genre='Mystery'].title from Authors
     WHERE name='Agatha Christie'

    … unfolds to:

    SELECT books.title from Authors
    LEFT JOIN Books books ON ( books.author_ID = Authors.ID )
      AND ( books.genre = 'Mystery' )  --> from Infix Filter
    WHERE'Agatha Christie';

    If an infix filter effectively reduces the cardinality of a to-many association to one, make this explicit with:

    SELECT name, books[1: favorite=true].title from Authors

    Exists Predicate

    Use a filtered path expression to test if any element of the associated collection matches the given filter:

    SELECT FROM Authors {name} WHERE EXISTS books[year = 2000]

    …unfolds to:

    SELECT name FROM Authors
            SELECT 1 FROM Books
            WHERE Books.author_id =
                AND Books.year = 2000

    Exists predicates can be nested:

    SELECT FROM Authors { name }
        WHERE EXISTS books[year = 2000 and EXISTS pages[wordcount > 1000]]

    A path with several associations is rewritten as nested exists predicates. The previous query is equivalent to the following query.

    SELECT FROM Authors { name }
        WHERE EXISTS books[year = 2000].pages[wordcount > 1000]

    Paths inside the filter are not yet supported.

    Casts in CDL

    There are two different constructs commonly called casts. SQL casts and CDL casts. The former produces SQL casts when rendered into SQL, whereas the latter does not:

    SELECT cast (foo+1 as Decimal) as bar from Foo;  -- standard SQL
    SELECT from Foo { foo+1 as bar : Decimal };      -- CDL-style

    learn more about CDL type definitions

    Use SQL casts when you actually want a cast in SQL. CDL casts are useful for expressions such as foo+1 as the compiler does not deduce types. For the OData backend, by specifying a type, the compiler will also assign the correct EDM type in the generated EDM(X) files.

    You don’t need a CDL cast if you already use a SQL cast. The compiler will extract the type from the SQL cast.

    Association Definitions

    Query-Local Mixins

    Use the mixin...into clause to logically add unmanaged associations to the source of the query, which you can use and propagate in the query’s projection. This is only supported in postfix notation.

    SELECT from Books mixin {
      localized : Association to LocalizedBooks on localized.ID = ID;
    } into {
      ID, localized.title

    In the select list

    Define an unmanaged association directly in the select list of the query to add the association to the view’s signature. This association cannot be used in the query itself. In contrast to mixins, these association definitions are also possible in projections.

    entity BookReviews as SELECT from Reviews {
      subject as bookID,
      book : Association to Books on book.ID = bookID

    In the ON condition you can, besides target elements, only reference elements of the select list. Elements of the query’s data sources are not accessible.

    This syntax can also be used to add new unmanaged associations to a projection or view via extend:

    extend BookReviews with columns {
      subject as bookID,
      book : Association to Books on book.ID = bookID