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 |
+-------+-------+
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:
MAX( PRIORITY ) as FINAL_STATUSdefine 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'