CDS View Syntax Options

[iperez-sofos]

Greetings.

I have a question about the syntax of the definition of CDS views.

The first option is the one shown in several examples of CDS views, as they appear on the SAP help pages:

VIEW MyView2 AS SELECT FROM Employee
{ officeId.building,
  officeId.floor,
  officeId.roomNumber,
  office.capacity,
  count(id) AS seatsTaken,
  count(id)/office.capacity as occupancyRate
} WHERE officeId.building = 1
  GROUP BY officeId.building,
           officeId.floor,
           officeId.roomNumber,
           office.capacity,
           office.type
  HAVING office.type = 'office' AND count(id)/office.capacity < 0.5;

The second syntax option (slightly different) is one I've seen used in a project I'm working on right now. It looks like this:

view AUX_BioBatchesCurves_H as
 select BB.ID as biobatchID,
        BB.primBatch.startingDate as startDate,
        HC.rcurve.idx.ID as idxID,
        HC.rcurve.idx.idxClass.item as idxClass,
        HC.rcurve.ID as RcurveID, // Curva de referencia
        HC.ID as HcurveID, // Curva Historica
        HC.baseValue as baseValue,
        HC.rcurve.idx.isRelative as isRelative,
        CASE
            when HC.rcurve.idx.idxClass.item = 'LOSS' then BB.primBatch.stage.mUnit.ID 
            else HC.rcurve.idx.idxUnit.ID
            end as cUnit,
        'HIST' as curveType : String(3) 
   from INDEXES.HCurves HC,
        PBATCHES.BioBatches BB,
        BioBatchSeasons BS
  where BB.primBatch.status.item = 'OPEN'
    and HC.hsgUnit.unit.ID = BB.housingUnit.unit.ID
    and HC.rcurve.breed.id = BB.primBatch.breed.id
    and HC.rcurve.gender.item = BB.primBatch.gender.item
    and BS.biobatchID = BB.ID
    and BS.seasonID = HC.rcurve.season.ID;

At first glance, they look like minor differences (such as the use of { } and the fields of the SELECT clause in different positions within the statement), maybe just a matter of style and without much impact, but since I really don't know if that's the case, I preferred to ask here in the community forum.

Concretely: Is there a difference and/or implications between the first and second syntax options?

Thanks in advance for any answers and/or suggestions.

[hjb · Accepted solution]

Hi, short executive answer: There are no differences in regards of SQL view generation.

Currently we have three different query 'flavors' which differ in supported features only

The query clauses are from the top of my mind and by no means complete or fully correct, just to illustrate the structural differences:

1) entity <name> as projection on <path>
    [ { ...} ]?
    [ excluding { ... } ]?
    [ where/group by/order by/having/limit ]?

Is the shortest form and intended for 1:1 projections and service exposures, creating updatable views. Any expression except for UNION, JOIN, where mixin, subqueries are supported.

2) The 'classic' CDS view definition with curly braces:

entity/view <name> as select from <from_block>
  [ mixin { ... } into ]? 
  [ { ... } ]? 
  [ where/group by/order by/having/limit ]?
  [ excluding { ... } ]?;

Is the full fledged view definition including UNION, JOINs, mixins, etc...

3) A short form of 2):

entity/view <name> as select <select_block>? from <from_block> 
<where>? <groupby>? <orderby>? <limit>?;

This is a courtesy to SQL aficionados. If the <select_block> is added between SELECT and FROM, no mixins are allowed.

entity E { key id: Integer; }
entity EP as projection on E;
entity V as select from E mixin {
 toE: association to E on toE.id = id;
} into { * };
entity SV as select from E ;

All produce the same SQL views (the mixin in V is not exposed):

CREATE VIEW EP AS SELECT
  E_0.id
FROM E AS E_0;
CREATE VIEW SV AS SELECT
  E_0.id
FROM E AS E_0;
CREATE VIEW V AS SELECT
  E_0.id
FROM E AS E_0;

Regards, Hans-Joachim

[former_member526112]

Hi

What is the type of join with the association along with mixin ?

Is it always left outer join? If yes how we can change to inner join?

Regards

Suresh