CDS View entity with join and literals Part 6 ABAP on HANA Course

Summary

This video tutorial covers enhancing a CDS (Core Data Services) view entity in SAP. It begins by explaining how to fetch data from multiple data sources using joins, including fetching from different tables like VBAK and VBAP. The video walks through creating select statements, adding conditions, using literals, and applying alias names for better readability. It also demonstrates how to preview data, apply conditions, and create a program to consume the CDS entity. The video concludes with insights into using different features, including select lists, aggregates, and the distinctions between CDS views and database views.

Takeaways

• 🛠️ The video focuses on enhancing a CDS view entity created in a previous video by joining multiple data sources (tables) to retrieve and display data.
• 🔄 The tutorial explains how to use joins in CDS view entities, specifically an inner join between VBAK and VBAP tables, which share a common sales order number field.
• 📋 Demonstrates the use of literals in field lists to display constant values in the output of a CDS view entity.
• 🚀 At the end of the tutorial, the CDS view entity is called in an ABAP program to retrieve and display data.
• 🧑‍💻 The CDS view can pull data not only from database tables but also from other CDS views, table functions, hierarchies, and other CDS-based views.
• 🔍 Shows how to preview the data after enhancing the view and applying inner joins, with alias names being used to make the fields more readable.
• 📊 The video emphasizes that the capabilities of SQL and Open SQL enhancements in HANA can be leveraged to create more powerful CDS view entities.
• ⚙️ Demonstrates how to use filters or WHERE conditions in the CDS view to retrieve specific data, such as filtering by a particular sales order.
• 📦 Aggregation, arithmetic operations, and other expressions (e.g., SUM, AVG, CASE, string operations) can be applied within CDS view entities similar to SQL select statements.
• 🚫 The SELECT * operation is not supported in CDS view entities but is allowed in DDIC-based CDS views, highlighting the difference between the two types of views.

Q & A

What is the main focus of the video?

The main focus of the video is to enhance a CDS view entity by getting data from multiple data sources using joins and displaying it in a preview. The video also covers the use of literals in the field list and demonstrates how to use the CDS view entity in an ABAP program.

What is a CDS view entity, and how is it used in this video?

A CDS (Core Data Services) view entity is a type of view in SAP that allows for complex data modeling and operations. In the video, the presenter uses it to join multiple tables, add additional fields, and perform various SQL operations. The result is then displayed in a data preview and used in an ABAP program.

Which tables are being used for the join operation in the CDS view entity?

The tables used for the join operation in the CDS view entity are `VBAK` (Sales Document Header) and `VBAP` (Sales Document Item), which are joined based on the common field `VBELN` (Sales Order Number).

What is the purpose of using literals in the CDS view entity?

Literals are used to define constant values in the select list. For example, the presenter uses a numerical literal (`0.23`) and a character literal (`'CDS'`) in the field list to demonstrate how to include constant values in the output.

What is the difference between using dot (`.`) and tilde (`~`) operators in the select statement?

In the video, the presenter explains that in CDS views, the dot (`.`) operator is used to refer to fields in the select list or join conditions instead of the tilde (`~`) operator, which is used in traditional ABAP SQL syntax.

What are the key differences between a CDS view entity and a traditional DDIC-based view?

The key differences include: (1) A CDS view entity cannot use the `SELECT *` syntax to fetch all fields, whereas a DDIC-based view supports it. (2) A CDS view entity cannot use a name list (a list of fields without alias names) in the field list, while a DDIC-based view can.

How do you display data from a CDS view entity in an ABAP program?

To display data from a CDS view entity in an ABAP program, the presenter creates a select statement that references the CDS view entity, selects specific fields, and stores the data in an internal table. The internal table is then passed to the `cl_demo_output=>display` method to display the data.

What are some common SQL operations demonstrated in the CDS view entity?

The video demonstrates several common SQL operations in the CDS view entity, including joins, aliasing fields, filtering data using the `WHERE` clause, and using literals and constants. It also mentions that arithmetic operations, aggregations (e.g., SUM, AVG), and string functions can be used.

Why is aliasing used in the CDS view entity, and what are its benefits?

Aliasing is used to provide meaningful names to columns or tables in a CDS view. This improves readability and makes it easier to understand the query. In the video, the presenter aliases the `VBAK` and `VBAP` tables as `header` and `item`, respectively, to represent the data structure more clearly.

What is the purpose of the `cl_demo_output` class in the ABAP program?

The `cl_demo_output` class is used to display the contents of an internal table in an ABAP program. In the video, the presenter uses the `cl_demo_output=>display` method to show the results of the CDS view entity in a readable format.

Outlines

00:00

📊 Introduction to Enhancing CDS View Entities

The speaker introduces the video, outlining its focus on enhancing a CDS view entity created in the previous session. They discuss retrieving data from multiple data sources and using joins in the CDS entity. The video will also cover how to display data using data preview, utilize literals in the field list, and call the CDS entity from an ABAP program for data display.

05:00

🔄 Expanding Data Retrieval with Joins

The speaker explains how to expand the CDS view to retrieve data from multiple tables (e.g., VBAK and VBAP) using inner joins. They emphasize that sales order tables share a common field (VBLN) and demonstrate how to write select statements and joins. Notable syntax changes, such as using a dot instead of a tilde, are also highlighted. Aliases can be used to give more meaningful names to fields like sales order.

10:02

🛠 Applying Conditions and Literals

The speaker discusses how to apply a 'where' condition in the CDS entity and retrieve specific data. They demonstrate the inclusion of literals (e.g., numerical and character literals) and explain the importance of assigning alias names to literals. The steps involve activating the CDS view, previewing the data, and applying these elements to a select statement to retrieve filtered data.

15:04

📋 Aggregations, Arithmetic, and Further Enhancements

This section elaborates on how the CDS view supports various SQL features, such as aggregations (e.g., sum, average), group by, string operations, and case statements. These features mirror what can be done in traditional SQL select statements. The speaker also touches on the use of CDS views in programs to retrieve and display data, treating them like database views but with more power.

💡 Alias Names for Meaningful Field Representation

The speaker demonstrates the use of alias names to represent fields more meaningfully, such as labeling the header and item data. These aliases help make table names clearer in the context of sales orders, differentiating between header and line item tables. After making the changes, the CDS entity is activated and tested.

📝 Writing and Executing an ABAP Program for Data Retrieval

In this part, the speaker walks through the process of writing an ABAP program to consume the created CDS entity. Instead of directly writing select statements for individual tables (e.g., VBAK, VBAP), the program selects from the CDS entity. The speaker demonstrates how to retrieve specific fields and use inline declarations to store the results.

🧪 Testing and Outputting Data from CDS Entities

The speaker tests the ABAP program by executing it and displaying the retrieved data using the CL_DEMO_OUTPUT class. They highlight how the program retrieves fields like sales order number and group using the CDS entity, mimicking typical database view behavior but with enhanced features available through the CDS model.

⭐ Differences Between DDIC-Based and CDS Entity Views

The speaker discusses a key difference between DDIC-based views and CDS view entities: the use of the 'select all' statement. While DDIC-based views support the 'select all' feature, CDS view entities do not. The video also clarifies terminologies such as 'field list' and 'name list,' and the speaker reiterates the limitations of the name list in view entities.

🚀 Final Thoughts and Next Steps

The speaker wraps up by summarizing the lesson on creating and using a CDS view entity, reviewing the syntax and features discussed. They announce plans for the next video, which will cover creating a parameterized CDS and using it in a program. Viewers are encouraged to like, subscribe, and continue learning through upcoming tutorials.

Keywords

💡CDS View Entity

CDS (Core Data Services) View Entity is a structured query view in SAP that allows users to define how data is retrieved and structured from multiple data sources. In the video, the CDS View Entity is enhanced by adding more fields and data from multiple tables using SQL joins. The script explains how to create and manipulate CDS View Entities for optimized data retrieval and presentation.

💡Inner Join

An Inner Join is a type of SQL operation that retrieves data from two or more tables based on a common field. In the video, the narrator demonstrates how to use an inner join to fetch data from two sales tables (VBAK and VBAP) using a shared sales order number field, allowing for a combined view of the data.

💡Field List

A Field List in a CDS view refers to the selection of fields that will be displayed in the output of a query. The video discusses using specific fields from different data sources and explains how to modify field names using aliasing to make the output more readable. This ensures that only relevant data is retrieved and displayed.

💡Literals

Literals are fixed values used directly in a query, such as numbers or text. The video illustrates how literals can be used in a CDS view to insert static values into the results, such as a numerical literal '0.23' or a character literal. The video also touches on the importance of aliasing literals for clarity in the output.

💡SQL Enhancement

SQL Enhancements refer to optimizations and additional features in SQL queries that are specific to SAP HANA. The video talks about leveraging SAP HANA's database capabilities for enhanced performance and flexibility in queries. This allows more complex operations like using joins, aggregations, and literals within the CDS views.

💡Alias

An alias is an alternate name given to a field or table to make the output more readable or the code more concise. The video demonstrates how to use aliases for table fields in the query, such as changing the default field name 'VBELN' to a more user-friendly alias like 'Sales Order.' Aliases are also used to distinguish between different data sources.

💡Data Preview

Data Preview is a feature that allows users to execute their CDS view or SQL query and see the actual data output. In the video, the data preview is used frequently to validate the correctness of the query and to ensure that the desired fields and data are retrieved as expected after changes are made to the CDS view.

💡Aggregations

Aggregations in SQL are used to calculate values like sums, averages, or counts over a set of data. The video hints at the ability to use aggregation functions in CDS views, such as SUM and AVG, to perform calculations on data. These aggregations are part of SQL enhancements available in SAP HANA for advanced data manipulation.

💡DDIC-Based View

DDIC-Based Views are views that are linked to SAP's Data Dictionary (DDIC) and support additional features like 'select all' queries. The video contrasts DDIC-based views with CDS views, explaining that the 'select all' operation is not allowed in CDS View Entities but is supported in DDIC-based views, making them slightly more flexible in certain cases.

💡Select Statement

A Select Statement is an SQL command used to retrieve data from a database. Throughout the video, the select statement is the core operation being enhanced in the CDS view entity. The narrator explains how to structure select statements with joins, where clauses, and field lists to retrieve and display data in the desired format.

Highlights

Introduction to enhancing a CDS view entity created in the last video.

Using joins in CDS views to get data from multiple data sources.

Explanation of using inner joins between VBAK and VBAP tables based on the sales order number.

Key difference in SQL syntax for inner joins: using dot notation instead of the tilde operator.

Applying alias names to fields to modify field descriptions in data preview.

Utilizing the WHERE condition in CDS views to filter data based on specific sales orders.

Demonstration of using literals in the CDS field list, including both numeric and character literals.

Applying arithmetic operations, aggregations, and string functions to the select statement in CDS views.

Creating a simple ABAP program to consume the CDS view entity and display data.

Key difference between CDS view entity and DDIC-based CDS views: SELECT * is not allowed in view entities.

Using alias names in the CDS view for meaningful naming of headers and line items.

Data preview process using right-click and F8 to display the selected data from the CDS view.

Literal values and their alias names are mandatory when used in select statements.

Explanation of field list and name list in CDS view entities, and their limitations compared to DDIC-based views.

Teaser for the next video: creating a parameterized CDS view and calling it from an ABAP program.

Transcripts

hello everyone welcome to sap techno

maniac in this video we will enhance cds

view entity which we have created in our

last video

we will get the data from the different

different data sources

i mean different different tables we'll

do the

we will use join in our cdsu entity and

we will get the data and we will try to

display the data in the data preview not

only that we will

learn how to use literals in our fill

list and at last we will call this

particular cdsu entity one of our about

program and we will do the data display

there

let's get started

we have created

one defined view entity in our last

video

so

let me improvise some let me add more

field instead of getting data from the

one field get

data from the multiple data sources

so this is up now you can see the data

source is the vba

so this data source can be either as of

now i'm using any database table

it can be any cdsu entity as well we can

get the data from another cds view

it can be cds table function as well

which we will going to use

we will going to learn in upcoming video

it can be cds hierarchy or any other cds

led ic based view as well so as of now

i am using normal database tables only

so

from here it's nothing just a select

statement

so we in our throughout our video course

we have learned the what all are the sql

enhancement done

in about hana so we can leverage the

hana database capabilities

so

this is not nothing much more than i can

say it's a select statement only let me

get some more uh more data

and let me get uh

face the data from more tables so it's a

select from vba k

uh i want to not only get data from the

vbe i want to get from the vb ap as well

so i i what i will do i will do the

inner joins since the sales order had a

table and sales order item table is

having the

web sales order number is common so what

i will do i will get from vbak then i

will do inner join

it's it's a normal join which we use

like in our select statement inner join

with vbap

and

simply if you are doing the inner zoning

we have to give some on conditions on

vb

ak

dot

vb eln

so here we have to use dot instead of

the tilt operator let's say one change

little bit little bit change not much

change from the select statement instead

of using tilde operator we have to use

dot

equal to

vba p

dot

vb

ln

so i did the

so let me clear out this one let me get

the key field is a vbln only so vba k

dot

vbln if you want to give alias name

alias name is you want to change the

description currently it is sales order

if you vba vbln if you want to give some

other description that also you can give

as

so

that means sales order and i want to get

the data some other data from the vbk

table dot

let me get some vkoz

control space here also work so we can

get the weekly rz is

also

oak

or you don't want to give aria's name

that is also fine

uh vba k

dot

vk

zrp

i don't want to give here vk i want to

get some data from vbap also vbap

dot posner

and

vba p

dot meta

so this data some of the data we got

from the

vbk table some of the data we got from

the vb epitome if i want to put wire

condition that also i can put where

vbln let me get execute this particular

select statement and get the data what

data i'm getting let me activate this

one ctrl f3

and let me do the

f8 to

preview this data so either i can do f8

or right click over here and i can see

open with the data preview also i can do

it will be

data will be previewed here so you can

see since i have changed the

vbl and name to s so using ali alias

name let me open it parallely so we can

visualize easily easily so that is the

reason now alias name is coming here

instead of the name of vbln and vkrz but

i didn't change here alias name that is

the reason it's came like directly vk

group posner and matna

so you can see these are the sales order

name

control c

now i what i want to do

i want to

put some wire condition as well so to

just to see how it will work

so it will be simpler just after the

fill list you have to give the wire

condition

vba k

dot vbln

equal to

i want to get the data for this

particular sales order only

so

as you can see this is the normal select

statement only which we have discussed

in one of the our first video

of the open sql announcement

so the

in this normal select statement what we

were doing select from vba k then we are

putting inner join here instead of this

curly bracket we were using the fill

list and where condition is usual and at

last we were doing into this some

internal table we are we were taking the

data so into internal table is missing

and apart from thing

apart from that thing that instead of

build list we are using the field fields

keyword we are using the this curly

bracket to get all the filled list

so this is the normal select statement i

can say

so whatever we can do on the normal

select statement most of the things we

can do

here as well for example we can do the

arithmetical operations let me get some

of the literals also i can get let me

get some of the literal as well 0.23

is

num

literal

but whenever we are using literal the

alias name is uh what we are giving is

alias name is mandatory let me get one

uh constant little little also if you

want to know about literals you can

watch out my uh literal video on sql

open and enhancement that i have

explained very well what is literal and

what is constant and what is the

difference between them

and

suppose i want to then

cds or something i can display on this

literal

is

character literal

and let me activate this one ctrl f3

and let me rephrase the data there

there is replace offering option is

there

ctrl f 3

and the refresh

or i can do data preview again not an

issue

f8

so you can see i got additional data and

where condition also applied i got the

data from only the

if only for the sales order number six

and i got numerical literal also

0.23 and uh character literal also

i got data like that i can get the data

from this particular

select statement

so as i told you we can do the

aggregations if you don't uh don't know

about what all are the things we can do

on the select statement you can watch

out this tutorial so uh i can say this

tutorial silly zone hana where i have

explained like that i i explain normal

uh sql state normal select statement

select a statement with the literal

authentical expression different kind of

sum

uh

average and those kind of thing

aggregate expression group by how we can

use and string operations case statement

call ask function cost expression these

all are the things whatever we are doing

most of the things we can do

in our select statement

we will try to explain here as well how

we can use those things here as well but

for the just the name sake because there

i explained in detail already all the

things whatever we were doing there all

the things we can apply here as well

this is the one thing now i have created

the cds view i just want to use the cds

view in our cds view entity specifically

in one of the our program i want to get

a fee instead of getting the data vba

and vbap since i have created this data

model i want to get the date directly

data from the data model safe or cdsu

entity one more important thing i didn't

use here alias name for the our database

table if i want to use i can use as a a

and instead of using uh

or i can tell the so

header

this is one of the another variant

not another variant if you want to use

some give some meaningful name instead

of header and item you can give like

this ctrl v

ctrl v

ctrl v

ctrl v

ctrl v

and ctrl v

sorry item data ctrl c

ctrl

v and ctrl v

and same goes with here control v

just i have given the alias name but

there is not nothing much difference so

that alias name whatever i have given

that i am using some meaningful name to

tell if you are not understanding with

the table name then we can it is a hider

table and it is the line item table

header underscore so also i can write

whatever whatever you want to write that

you can write c

control

control f3

so let me call this particular cdsu in

one of the select statement so i have to

create one program obviously

uh let me create one program

uh in this in this package itself which

i am currently using

other repository object

and you have to search for program

about program

uh i can give any name g underscore cds

consumption

or anything i can you you whatever name

you want to give

you can give you have to click on next

button

give some appropriate tr

and click on finish button it will

create normal about program

so this is i can say this is normal

about program now what i will do instead

instead of getting the data

writing select statement on vba and vbap

like

we have created

you can assume like that we have created

one view database view and we are

calling that database you instead of uh

hitting those different different tables

directly i'm calling from the database

view and getting the data it's a work

like this only but it is much powerful

than the database view as we already

have seen in

in our previous videos

so let me get data from here so normal

select statement how we like how we

write select

from

select from which is i have to use this

cds entity select from this particular

cds entity

from uh and

fields i can tell instead of getting all

the fields i can get the

s so

comma

sells c

ctrl v

or i can say

vk group

grp

these are the fields i want to get

select from this fields into

inline declaration into table

hit the rate data

i t

underscore aim

i did inline declaration

and if

again i will use one of my template it

should be very useful

if s is initial what i need to do

obviously i want to

review this data so cl underscore demo

underscore output

like we usually do

i just call this

output

display method

space

shift enter

in the display method i can pass this

table ctrl c

ctrl

f1

ctrl f3

let me do

f9

this

so you can see i got data for particular

sales oh and 1703 i think it's almost

uh let me get data

only vk group i i have got i didn't get

all the data instead of fill list i can

tell i want to get all the data

is it giving any error over here no

control f3

and clear out the console

and let me do f9

so i got all the data i got the literal

value vk group posner and whatever the

data is it were there all the data i

have got and i have displayed in the

like that

like a simple database table we can use

a cds entity as our data shows when we

are writing the select statement but one

more thing as i told you

uh

this is no nothing much difference when

we

see that select from the field list as i

told you select from instead of 12 list

we were displaying this this we are

giving the fill list in the our

curly bracket and where condition also

we were putting and here we don't have

any into close we are just directly

doing the data preview

one most important thing if i try to use

instead of this all the things if i try

to use here star i want to get all the

data but in the define view entity it is

not supported the select

all can't be used in the defined view

entity but

if i want to use the select all in the

ddic based cds view that i can do that

is again one of the difference i might

have missed in my last video uh you if

you want to use ddic based view if i

want instead of getting the this is the

basically ddic based view which we have

discussed in our previous video if i

want to get instead of single field i

want to get all the fields uh

this uh again one more correction i want

to do last video i called this is a

filled list it's not build list it's a

name list we call nameless which is not

supported in our view entity this is the

name list and this is the field list

whatever we are defining inside the kali

packet this is name list which is uh

which is now not supported in the

view entity but the name list is

supported in the

our ddic based view same way the select

all also supported now i want to remove

this name list

and one more error i'm getting might be

the buffering related error i will

just comment it out controls if less

than i don't want to buffer the data let

me see you can see i'm not getting any

error now

but if you use select all in the cds

entity this view entity then you will

you will not able to use select all

inside this select all is not supported

for this defined view entity but it is

supported for the ddic based view and

one as i told you last minute uh last

video the filled list is not filled list

name list again i'm repeating this is

the field list and whatever we are using

here control z control z

whatever we are using after this alias

name that is called the name list and

control z and this is our builder list

so name list also

not supported in the

view entity means what we can say this

which is views which is not based on the

ddic in this video we have learned about

normal cds view entity syntax how to

create

the cdsu entity with the basic example

and we have called that cds view entity

in one of the our program

in my next video we will create one

parameterized cds and we will try to

call that parameter series in one of the

our program before going to that video

please like this video subscribe this

channel with that thank you and happy

learning