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' |
