CDS

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

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'

 

Show More

Related Articles

Leave a Reply

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

Back to top button
Close

Adblock Detected

Please consider supporting us by disabling your ad blocker