abapcds

CDS Association with where condition containing a SELECT

I have two views :

ZC_PurRequisitionFs and ZMM_ONAYT005.

First view extended from C_PurRequisitionFs.

Second view gets data from Z* table.

How can I get only first view data that does not exist on second view ?



@AbapCatalog.sqlViewAppendName: 'ZCPURREQUISFS'
@EndUserText.label: 'Sat belgeleri'
extend view C_PurRequisitionFs with ZC_PurRequisitionFs {
    *
} where ZC_PurRequisitionFs.object_id not in( SELECT * FROM ZMM_ONAYT005 ).
Solution

  • While CDS views do not support subqueries, they do support JOINs.

    Usually you would use a JOIN to get only those entries which exist in both tables. But when you want all entries from table A which don't exist in table B, you can do a left outer join and then add a where-condition for only entries where the right table is null.

    define view Z_TEST as select 
from table_a
left outer join table_b on 
    table_a.object_id = table_b.object_id
{
    ... fields....
}
where table_b.object_id is null;