Database independent code to data Flashcards
Name the 3 parts of SQL
DML - Data manipulation
DDL - Data definition
DCL - Data Control
What parts of SQL does Open SQL cover?
A small part of DML.
What part of SQL is handled in SAP by the data dictionary
Only a small part of DDL
How much of DCL is implemented in SAP prior to NW 7.4 sp05?
Almost nothing. The DCL would normally be used for user control but in SAP the database only knows one user
What is the single database user of an SAP system
The user is SAP{sid} or SAPR3. All data is stored in this accounts schema
What are two examples of differences in new Open SQL
Fields must be comma separated, Host variables must be used
In new Open SQL mathematical operations are available on all operand types?
False, there are many limitations. For example, MOD is only allowed on types I and P without decimals. I believe AVG must always be cast to type F
The CDS consist of what three things?
DDL
QL (from the ABAP code)
DCL
In comparison to ABAP DICTIONARY views, what additional functions do CDS views
Outer joins and unions are supported
Calculation expressions, aggregations, and groupings are supported
Nested views are supported
From least to most advanced, list the ways views can be created
SE11
CDS
SQL92
HANA Column views
CDS views support all facets of SQL92 and HANA specific features?
That’s a big “NO” on both accounts
What is the path in eclipse to create a CDS
From the ABAP project
New->Other ABAP object->Dictionary->Data Definition
It is not possible to see the CDS view in the ABAP dictionary?
False, the CDS view shows up in the dictionary as a DDL SQL View with the name given in the CDS definition as the sqlViewName.
The sqlViewName is always the same as the name given in the “define view” statement?
It is never the same. Both names must be unique across the system
There are two ways to see the native SQL used to create the CDS view on the database. What are they?
From the ABAP dictionary extras->create
From the CDS definition press F3 while on the sqlViewName. Click on the presented link
CDS views cannot be buffered!
Of course they can
buffering. status
buffering. type
How can reference information, such as currency and unit of measure info be added to a CDS definition?
Using annotations such as
@semantics.amount.currencyCode: ‘CURRENCY’
@semantics.currencyCode: true
By default, CDS views are client dependent?
True, but this can be changed on each individual CDS by setting
@ClientDependent: ‘false’
In a CDS can CAST expressions be nested?
No
What does CDS stand for?
Core Data Services
What does the COALESCE do?
Can replace null values in a list
Cast types in CDS views are defined using
abap. decfloat34
abap. cuky
abap. fltp……
You can use either he CDS view name or the SQL view name in the SELECT clause?
Not anymore, only the CDS view name can be used as of NW7.5
When using an exposed association over an adhoc association, what advantage is gained?
The entire exposed association can be referenced by name instead of individual columns
I don’t understand how to use exposed associations
It seems like a new view can select from another CDS view and the associations can be addressed simply
When using path expressions what must the association names be prefixed with? This seems to be related to addressing association fields from ABAP
A backslash _association-field
I don’t understand the difference between exposed and ad-hoc associations
An adhoc is a list of fields from the associated table or view or cds
An Exposed is the entire row. The pre-req for the exposed is to have the “on” field from the left hand side of the association listed as the field above the associated entity in the field list.
This EXPOSES the entire entity row of the associated entity
You can use SELECT * when employing a UNION or UNION ALL
No
A field of the right table must be included in the ON clause?
It says no but I don’t understand how that works
INTO no longer needs to be at the end of the Select
True except in UNIONS where it must still be the last clause
DCL source is?
The data control represented in the ABAP dictionary
What is the difference between an Association and a View
Associations use foreign key relationships to avaid having to define the view relationship
What is the difference between an Association and a View
Associations use foreign key relationships to avaid having to define the view relationship
An Association can specify cardinality
Where can an association be used
in all places where table names or column names are used
How can exposed associations be used
In data preview, and also in subsequent CDSs. From open SQL they can be addressed thru path expressions
Is it possible to extend an existing CDS view without modification?
Yes, use the EXTEND functionality
Parameters of a CDS must be of what type?
Predefined or data element examples WITH PARAMETERS FIRSTNAME: type bname MYAGE: type abap.int8
Parameters of a CDS must be of what type?
Predefined or data element examples WITH PARAMETERS FIRSTNAME: type bname, MYAGE: type abap.int8
True or false, the parameters in the parameter list must be comma separated?
True
How can a system variable be used as a CDS parameter?
Using the annotation
@Environment.SystemField: #{sys field}
How can a system variable be used as a CDS parameter?
Using the annotation
@Environment.SystemField: #{sys field}
THIS ONLY WORKS IF THE CDS IS ACCESSED THRU OPEN SQL
When a CDS uses another CDS that has input parameters, how are the parameters passed?
as comma separated value pairs enclosed in parentheses.
e.g.
( firstname ‘Bob’, lastname ‘Heide’)
System filled parameters DO NEED to be passed
When Open SQL uses a CDS that has parameters, how are the parameters passed?
as comma separated value pairs enclosed in parentheses.
e.g
( firstname = ‘Bob’,
lastname = ‘Heide’ )
System filled parameters do not need to be passed
How can access control be implemented for CDSs?
Access control is implemented by
Defining a role and granting access to the CDS
The values are as an aspect in a standard authority check
What is the only current auth aspect that is supported
pfcg_auth
For authorizations, two annotations are available in the DCL. What are they and what do they mean?
@EndUserText.label: ‘this is the role text’
@MappingRole: role is assigned to all users regardless of client. Only value allowed is true
For authorizations, one annotation with three options is allowed in the DDL of the CDS. Name them and what they do.
@AccessControl.authorizationCheck #CHECK means perform the access control using the assigned role. Syntax error if no role assigned #NOT_REQUIRED is just like #CHECK but does not give a syntax error if no role is assigned #NOT_ALLOWED gives a syntax check warning if a DCL is assigned
What must every CDS role contain?
@MappingRole: true
