CDS Views Basic Syntaxes : – Select *, Select columns, Select distinct, WHERE clause, Table Aliases, Key Elements ,DDL Syntax Rules, CREATE SQL statement

1. CDS View – Select *
Select * – selects all columns from the underlying data source.

@AbapCatalog.sqlViewName: 'S4HCDS_SEL_ALL'
define view S4hcds_Select_All as select from sflight 
{
  *
}

Output in Data Preview:

2. CDS View – Select columns

Comma separated list of names
optional alias names with keyword AS

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V02'
define view s4hcds_sel_v2 as
select fldate as Flightdate, connid as Flightno from sflight

Alternative Syntax
select list after from clause enclosed in curly braces {….}

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V03'
define view s4hcds_sel_v3 as
select from sflight
{
   fldate as Flightdate,
   connid as FlightNumber
}

3. CDS Views – Select columns

Literals are only supported:
as C-Sequence Literals (Max length 1333)
as signed integer Literals (4-Byte)

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V04'
define view s4hcds_sel_v4 as select from sflight
{
   carrid as FlightCarrierID,
   'CarrierDescription' as descr
}

4. CDS Views – Select distinct

Duplicates can occur when a key column is not included in the projection list

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V06A'
define view s4hcds_sel_v6a as select from spfli
{
   cityfrom
}

Output:

The keyword DISTINCT ensures that the result table contains no duplicates:

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V06B'
define view s4hcds_sel_v6b as select distinct from spfli
{
   cityfrom
}

5. CDS Views – WHERE

You can use compound WHERE clauses.

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V07'
define view s4hcds_sel_v7 as select from sflight
{
   carrid, connid, planetype, seatsmax
}
   where planetype ='A340-600' and seatsmax = 330

6. CDS Views – Table Aliases

Table aliases are defined in the FROM clause.
You can use table aliases in the projection list.

Here below we have ‘c’ as Table Alias for table scarr

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V09'
define view s4hcds_sel_v9 as select from scarr as c
{
   c.carrname as Carrier,
   c.carrid as ID
}

7. CDS View – Key Elements

Key elements should be continuous and placed at the start of the list.

CDS View key fields need not be same as of the underlying data source.

Key fields for the generated SQL view is not derived from the key fields of the CDS view.

@AbapCatalog.sqlViewName:'S4HCDS_BOOK'
define view s4hcds_Booking
  as select from sbook
{
   key carrid,
   key connid,
   fldate,
   bookid,
   class
}

General DDL Syntax Rules

  • Keywords: Keywords must be all uppercase, all lowercase, or in lowercase with an uppercase initial letter. Mixed uppercase and lowercase is not allowed.
  • Names: Names are not case-sensitive. A name can have a maximum of 30 characters.
  • Literals: Number literals must always be specified in full and a decimal point (.) used as a decimal separator if necessary. Character literals are enclosed in single quotations marks (‘).
  • Comments: Two forward slashes (//) introduce a comment, which continues until the end of the line.Comments within lines or that span multiple lines are enclosed by the characters /* and */.
  • Separators: Statements can be closed using a semicolon (;). This is optional.
  • Protected Words: Certain keywords are protected and cannot be used as self-defined names.

Syntax Rules – Examples

SELECT, select, and SelectValid ways of specifying a keyword.
seLect and SeLect Invalid, produce syntax errors.
MYVIEW, myview, and myView all identify the same operand.
0.5 is a valid number literal, but .5 is not.

How to consume CDS view in a report?

REPORT Y_CDS_CONSUMPTION_EXAMPLE.
DATA lt_result TYPE STANDARD TABLE OF s4hcds_Booking.

SELECT * UP TO 10 ROWS
   FROM s4hcds_Booking
   INTO TABLE @lt_result
   WHERE bookid BETWEEN '25' and '30'.

cl_demo_output=>display_data( lt_result ).

Generated SQL Statement for CDS View

Two ways to get the generated CREATE SQL statement in the database for the CDS view
1. Right Click on the CDS source editor in Eclipse and select “Show SQL CREATE Statement”.
2. Open the generated database view in SE11 and select Menu -> Extras -> CREATE Statement.

@AbapCatalog.sqlViewName:'S4HCDS_SEL_V09'
define view s4hcds_sel_v9 as select from
scarr as c
{
  c.carrname as Carrier,
  c.carrid as ID
}
CREATE VIEW "S4HCDS_SEL_V09" AS SELECT
  "C"."MANDT" AS "MANDT",
  "C"."CARRNAME" AS "CARRIER",
  "C"."CARRID" AS "ID"
FROM "SCARR" "C"

 

 

Advertisement

Leave a Comment