CDS View entity with join and literals Part 6 ABAP on HANA Course
Summary
TLDRThis 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
📊 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.
🔄 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.
🛠 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.
📋 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.
Mindmap
Keywords
💡CDS View Entity
💡Inner Join
💡Field List
💡Literals
💡SQL Enhancement
💡Alias
💡Data Preview
💡Aggregations
💡DDIC-Based View
💡Select Statement
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
Посмотреть больше похожих видео
CDS View with input parameters Part 7 ABAP on HANA Course
Association in cds view abap CDS Part 9 ABAP on HANA Course
Generate C# Model from existing Microsoft SQL Server Database
Learn Basic SQL in 15 Minutes | Business Intelligence For Beginners | SQL Tutorial For Beginners 1/3
HTTP | Mastering React: An In-Depth Zero to Hero Video Series
view data codeigniter v3
5.0 / 5 (0 votes)