Get single main status from JEST table based on priorities?

Question

For each object number in JEST table I want to get a single status that I consider my main status.

The main problem is that some status that in my case I consider unique like I0076 (deletion flag) can be active at the same time than others like created (I0001). In this scenario I would like to use GROUP BY objnr and then use a CASE in the status where I0076 always overrides any other status.

Is that possible with ABAP CDS or SQL? Or do I have to write ABAP code (AMDP or post processing) to solve this?

Input:

+-------+-------+
| OBJNR | STAT  |
+-------+-------+
| OBJ1  | I0001 |
| OBJ1  | I0076 |
| OBJ2  | I0001 |
+-------+-------+

Output:

+-------+-------+
| OBJ1  | I0076 |
| OBJ2  | I0001 |
+-------+-------+

Answer 1

You can build your status priority in your CDS view definition with aggregation on CASE condition and COALESCE. If your statuses are split into two groups of mutually exclusive statuses (say "overriding" and "non-overriding"), then it is enough to have only one COALESCE and one CASE.

Like this:

define view z_demo_v as select from jest {
  OBJNR,
  coalesce(
// Overriding statuses
    max( case when STAT = 'I0076' /*and other overriding values go here*/ then STAT end ),
// Non-overriding statuses. All overriding are captured in previous CASE
//so no need to exclude them here, coalesce will return the first MAX
    max( STAT)
  ) as STAT
}

  where INACT = ''
  group by OBJNR

If you can have more than one status in each group, then you can nest as much coalesce as you need putting the highest priority statuses first (because in CDS coalesce function accepts only two arguments, but in SQL it can have many).

define view z_demo_v as select from jest {
  OBJNR,
  coalesce(
    coalesce(
      // Status with highest priority
      max( case STAT when 'I0076' then 'I0076' end ),
      // Status with second-highest priority
      max( case STAT when 'I0070' then 'I0070' end )
    ),
    // Status with other priority (that should be mutually exclusive)
    max( STAT)
  ) as STAT
}

  where INACT = ''
  group by OBJNR

Of course, it will need to be updated for new statuses, but because CDS syntax is limited, I do not see lightweight options.

For more robust solution you can create another CDS view which will contain status priority with the structure:

| STATUS   | PRIORITY |
| (CHAR 5) | (INT)    |
+----------+----------+
| I0001    | 1        |
| I0011    | 2        |
| I0076    | 100      |
| I0079    | 101      |

where each overriding statuses will be placed with higher priority and priority should uniquely identify status. It may be taken from some Z table where you directly put priority or from customizing settings if they are available or from your coded AMDP (for example, substringing last 3 characters and ordering by them via row_number() function).

Then you need to have two views joined with that view:

• First view will translate status code to status priority and calculate the most prioritized status: MAX( PRIORITY ) as FINAL_STATUS
• Second view will translate it back: FINAL_STATUS -> PRIORITY -> STATUS.

define view z_stat_prio_v as select from z_statprio_t {
  STATUS,
  PRIORITY
}

define view z_demo_stat_last_v as select
  from jest as j
  join z_stat_prio_v as p
  on j.stat = p.status
 {
  j.OBJNR,
  max( p.PRIORITY ) as FINAL_STATUS
}

  where j.INACT = ''
  group by j.OBJNR


define view z_demo_stat_v as select
  from z_demo_stat_last_v as s
  join z_stat_prio_v as p
  on s.final_status = p.priority
 {
  s.OBJNR,
  p.STATUS as STAT
}

UPD: If you always have overriding status (I0076 in your case) after any general status (I0001), then you can use LEFT OUTER JOIN with the same JEST table filtered in-place. Below DDL source was successfully activated in SAP_ABA 750 SP 08.

define view Z_TEST_V as select from JEST as j1
  left outer join JEST as j2
    on j1.OBJNR = j2.OBJNR
     and j2.STAT = 'I0076'
     and j2.INACT = ''
  {
  j1.OBJNR,
  coalesce (
    j2.STAT,
    j1.STAT
  ) as STAT

}
  where j1.INACT = ''
    and j1.STAT <> 'I0076'

Answer 2

You can achieve this by using three CDS views.

• In the first CDS view you use a where statement to select only the deleted status (where stat = 'I0076')
• In the second CDS view you use a where statement to select only the non-deleted status (where stat <> 'I0076')
• In the third CDS view you use a left outer join on the first and second view and add a case statement to only include one of the results (case when First.stat is null then Second.stat else First.stat end as status)