Pass select option to CDS View only! No AMDP

[former_member533232]

I know we can pass Select options from ABAP to CDS using table function and AMDP approach,

But as we know there are advantages of using core CDS and I was wondering that is there any approach where I can pass my select option to CDS view and consume it in CDS view only

[maheshpalavalli]

Can u provide more information about the question,:

1. From where are u executing the CDs view

2. From where u want to pass the select options data

[pfefferf]

What avoids to use the Select Option in a where condition when you query from the CDS view in your ABAP logic?

[former_member533232]

@maheshkumar.palavalli:

I am calling it from ABAP report and want to pass the values from there

[former_member533232]

@florian.pfeffer

Basically its a performance issue.

I understood the concept of applying select option in calling level at ABAP layer such as below

SELECT * FROM z_cds_view INTO TABLE @DATA(lt_result) where field1 in SelOpt_field1.

here complete z_cds_view will be executed without any where clause at DB level woudn't it affect the performance, rather than paassing a filter directly to CDS?

[former_member533232]

@Florian Pfeffer Can you please elaborate a little as I am trying to figure the same

[gasparerdelyi]

What do you mean by passing? Would you like to do some calculations in ABAP CDS based on the content of the SELECT-OPTIONS? Or just apply as filter?

[former_member533232]

@Gaspar Zoltan Erdelyi As a filter only, to improve performance by applying filter at DB level

[MateuszAdamus]

Hello amit316chauhan

Condition generated from Select Options is passed to the DB server and handled on the DB side. Records are not filtered in the application server. Please read the SAP Help documentation when not sure.

I see no reason why the same thing shouldn't happen when querying data from a CDS view.

Kind regards,
Mateusz

[former_member533232]

I Aggre that the condition passed is executed at DB layer

   SELECT *
       FROM z_cds_view
       INTO TABLE @DATA(lt_result)
       where field1 in s_field1. (Here Where Clause will be executed at DB level)

In Above code Complete z_cds_view will gets executed(lets say it retrieve 1Mil records) without filter first and then its result(1M records) will be filtered again (at DB level) by passing s_field1.

So the performance could have been improved if there would have been a way to pass select option directly at DB level, can you please comment on same?

Kind Regards

[gasparerdelyi]

The WHERE condition will be applied by DB the way the optimizer can deal with the situation.
It will not pass non-matching records to the application server.

If it will process many records or not depends on a number of factors:

• If it is a calculated field
• if the field is coming from a join or association then what are the ON conditions
  

just to name the trivial ones...
Why filter pushdown is not working is sometimes a not so easy question... but far more details are needed for analysis.

[keremkoseoglu]

Here is an ugly but functional workaround. Assuming that you have a select option for BUKRS;

• Generate a GUID
• In your Z-program, SELECT bukrs FROM t001 WHERE bukrs IN s_bukrs INTO TABLE whatever
• Insert those values in a temporary Z-table having two keys: GUID + BUKRS
• Pass this GUID to your CDS View
• In the CDS view, inner join your Z-table with GUID + BUKRS

[former_member533232]

That is one hell of a walkaround 🙂

Have you seen this solution in a realtime application?

Also this solution will have its cons for performance :

1.Updating a table for guid

2. if table is Standard, then we have to add extra field

[keremkoseoglu]

Not exactly that, but putting data into temporary tables and joining via GUID's is something doable. We did that occasionally (before the era of CDS Views) when we needed to use SELECT SUM and FOR ALL ENTRIES together.

1) You also need to cleanse the old entries from time to time.

2) The table would be a Z-table, not a standard table.

[former_member1716]

amit316chauhan,

If you are looking for an alternate approach for using select option inside CDS other than AMDP, well i don't think you have a better approach.

On the other hand when you query upon a CDS using select query you can very well use the Select options on them just like how you use up on a back end table.

Are you expecting something else other than this? if so please explain.

Regards!

[former_member533232]

I understood the concept of applying select option in calling level at ABAP layer<br>

SELECT *
       FROM z_cds_view
       INTO TABLE @DATA(lt_result)
       where field1 in s_field1.

here complete z_cds_view will be executed without any where clause at DB level woudn't it affect the performance, rather than  paassing a filter directly to CDS?

[former_member1716]

amit316chauhan,

In that case CDS with table functions is the only known method so far.

Regards!

[former_member533232]

So basically AMDP is the only solution, so in the above-mentioned query, the complete table will be triggered right?

and that would be expensive on DB load? can you comment on performance perspective?

[former_member1716]

amit316chauhan,

When you apply a select option in CDS you are only applying the filter on ABAP layer after projecting the data from database, similar to what we do with normal Database tables in ABAP.

But with CDS with table functions we are pushing down the filter to the DB Layer.

So Yes, CDS with table functions has better performance.

Regards!