How to consume a CDS from ABAP SQL using an association value in the WHERE condition?

Question

I have a CDS view for notifications header with an association to their status

define view ZNOTIF as select from qmel as notif
    association [0..*] to ZNOTIF_STATUS as _status on _status.object_num = notif.objnr
{
    key notif.qmnum        as notif_id,
    notif.objnr            as object_num,
    notif.qmart            as type,
    notif.qmtxt            as description, 
    _status
}

Now I would like to consume this CDS in ABAP selecting all the notifications with an specific status (and without incrementing cardinality if possible).

Something like this, but of course this has a syntax error:

SELECT notif_id,
       type,
       description
  FROM ZNOTIF
  INTO TABLE @DATA(notifs)
  WHERE \_status-status_id = 'STATUS_FILTER_VALUE'. "Syntax error

Can I do that somehow?

score 2 · Accepted Answer · answered Apr 03 '20 at 03:02

2

SELECT DISTINCT notif_id,
   type,
   description
FROM ZNOTIF
WHERE \_status[ (*) ]-status_id = 'STATUS_FILTER_VALUE'
INTO TABLE @DATA(notifs).

answered Apr 03 '20 at 03:02

Haojie

5,665
1
15
14

Thanks! Works like charm. As I expected it increases the cardinality even if the association fields are not within the SELECT, but then adding DISTINCT the problem is solved. – RaTiO Apr 03 '20 at 07:57
@RaTiO you are welcome. yes, indeed. filter on associations has to increase the cardinality – Haojie Apr 03 '20 at 07:59

How to consume a CDS from ABAP SQL using an association value in the WHERE condition?

1 Answers1

Linked