SAP ABAP CDS SQL Expressions – CASE expressions ( Simple CASE, Searched CASE & Nested CASE ), COALESCE

CDS SQL Expressions

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


  • 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
define view s4hcds_sel_v11 as select from spfli
  case carrid
       when 'AA' then 'Prio1'
       when 'LH' then
         case connid
              when '0400' then 'Prio2'
              else 'Prio3'
       else 'Unknown'
  end as Priority


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 = b.customid
{ as Customer_Name, as Customer_city,
coalesce( b.invoice , 'NULL') as invoice
where b.bookid = '00002406'

What do you think?

Written by admin

Leave a Reply

Your email address will not be published. Required fields are marked *

CDS SQL Expressions

CDS Annotations

CDS SQL Expressions

CDS Conversion Functions (CAST, Unit Conversion, Currency Conversion)