SAP ABAP CDS Functions
CDS SQL Expressions or Query – CASE expressions ( Simple CASE, Searched CASE & Nested CASE ), COALESCE
Generic SQL Expressions
CASE Expressions
3 types of CASE expressions we have that are mentioned below:
Simple CASE Expression
- The projection list can contain columns that are based on a case differentiation.
- These columns can be named explicitly
- It works like normal CASE statement in ABAP.
- Example: The output is list of columns from table SBUSPART resulting TYPE as customer or Travel Agency based on the TypeCode.
@AbapCatalog.sqlViewName: 'S4HCDS_SEL_VD10'
define view s4hcds_sel_v10 as select from sbuspart
{
buspartnum as ID,
contact,
contphono,
case buspatyp
when 'FC' then 'Customer'
when 'TA' then 'Travel Agency'
else 'NA'
end as Type
}
Searched CASE Expression
- It Operates like an If … elseif construct.
@AbapCatalog.sqlViewName: ‘S4HCDS_SCASE'
define view s4hcds_searched_case as select from spfli
{
key carrid,
key connid,
distance,
distid,
case
when distance >= 2000 then 'long-haul flight'
when distance >= 1000 and distance < 2000 then 'medium-haul flight'
when distance < 1000 then 'short-haul flight'
else 'error'
end as flight_type
}
Nested CASE Expression
Example
- Based on the flight details give priority as follows:
- Carrid= AA => Priority 1
- Carrid= LH and connid =400 => Priority 2
- Carrid= LH and connid=except 400 => Priority 3
- Carrid = others => Priority Unknown
@AbapCatalog.sqlViewName:‘S4HCDS_SEL_VD11'
define view s4hcds_sel_v11 as select from spfli
{
carrid,
connid,
case carrid
when 'AA' then 'Prio1'
when 'LH' then
case connid
when '0400' then 'Prio2'
else 'Prio3'
end
else 'Unknown'
end as Priority
}
COALESCE Function
COALESCE( arg1, arg2 ) – returns the value of the argument arg1 (if this is not the null value); otherwise it returns the value of the argument arg2
COALESCE can be used to set reasonable default values for the NULL values
AbapCatalog.sqlViewName: 'S4HCDS_SEL_VD13'
define view S4hcds_sel_v13 as select from scustom as c
left outer join sbook as b on c.id = b.customid
{
c.name as Customer_Name,
c.city as Customer_city,
coalesce( b.invoice , 'NULL') as invoice
}
where b.bookid = '00002406'
Advertisement