Solved: Questions on CDS Views with input parameters

Former Member · ‎2015 Jan 19

Dear Experts,

1. Can we call a CDS View (or generated database view) with input parameters inside an AMDP? I am looking for something similar to the feature in HANA where we can consume calculation view with filters inside SQL Script?

2. I understand we can next CDS Views, but how we can next (call) a CDS View with input parameters inside another CDS View?

Thanks,

Giri

thomasgauweiler · ‎2015 Jan 19

Dear Giri,

1. you can call a CDS view with parameters from an AMDP but you need to know how the CDS view is mapped to the database. They are generated as so called "table functions" using the name given in the @AbapCatalog.sqlViewName annotation.

So your call in an ADMP has to look like this :

SELECT * FROM sqlViewName( parameter => value ) WHERE ...;

2. When you nest CDS views with parameters, you have to pass parameters to the inner view (either from your own parameters or as constants). This will then look like this:

define view outer_view with parameters p1 : ... as select from inner_view( p1 : $parameters.p1 )

Please have a look at your ABAP online help. The details are described there.

Best Regards, Thomas

thomasgauweiler · ‎2015 Jan 19

Dear Giri,

1. you can call a CDS view with parameters from an AMDP but you need to know how the CDS view is mapped to the database. They are generated as so called "table functions" using the name given in the @AbapCatalog.sqlViewName annotation.

So your call in an ADMP has to look like this :

SELECT * FROM sqlViewName( parameter => value ) WHERE ...;

2. When you nest CDS views with parameters, you have to pass parameters to the inner view (either from your own parameters or as constants). This will then look like this:

define view outer_view with parameters p1 : ... as select from inner_view( p1 : $parameters.p1 )

Please have a look at your ABAP online help. The details are described there.

Best Regards, Thomas

Former Member · ‎2015 Jan 19

Hi Thomas,

I get the below error that the CDS View's generated table function cannot take field from AMDP.

I have a requirement to query the CDS View using 2 timestamps (start & end). So, in AMDP I have used the TIME_STAMP and try to pass it to the CDS View

View code:

define view Z_Ngi_Cag_A

with parameters start_ts:abap.dec( 15, 0 ) , end_ts:abap.dec( 15, 0 )

as select from table {

resource_key

}

where

(begtstmp > $parameters.start_ts or endtstmp > $parameters.start_ts )

and

(begtstmp < $parameters.end_ts or endtstmp < $parameters.end_ts )

I am calling using the below AMDP:

DECLARE iv_start_date TIMESTAMP;

DECLARE iv_end_date TIMESTAMP;

SELECT CURRENT_TIMESTAMP INTO iv_start_date from dummy;

SELECT ADD_DAYS(CURRENT_TIMESTAMP, 30) INTO iv_end_date from dummy;

et_data = SELECT * FROM ZMR_H_CA ( start_ts => iv_start_date, end_ts => iv_end_date );

What could be wrong ? Is this not supported?

Thanks,

Giri

thomasgauweiler · ‎2015 Jan 19

Hi Giri,

you have to put a colon ':' in front of your variable names:

et_data = SELECT * FROM ZMR_H_CA ( start_ts => :iv_start_date, end_ts => :iv_end_date );

Best Regards, Thomas

Former Member · ‎2015 Jul 16

Hi Thomas,

I know this may sound silly , but I'm new the ABAP on HANA development.
Could you clarify as to why the input parameters in AMDP have to have a colon before the name?

jasmin_gruschke · ‎2015 Jul 16

Hi Johan,
has nothing to do with you being new to ABAP... it's actually SQLScript embedded in ABAP :-).

In SQLScript (similar in SQL) you have to mask host variables with a dedicated character, which is the colon. With the new Open SQL syntax (in ABAP 7.4) we have something similar with the "@" sign in place, see e.g. the section in http://scn.sap.com/community/abap/blog/2014/02/06/abap-news-for-release-740-sp05.

Cheers,
Jasmin

former_member204156 · ‎2015 Nov 25

Hi Jasmin,

I am new to SAP HANA. I have learn from your open sap videos that we have some code push down techniques like CDS views and AMDP's.

So could you please explain, what is the difference among CDS views and AMDP's ; i.e; in which scenarios we use them ? and which is the best among those two??

Thanks in Advance..

Naveena M.

sivapalani10 · ‎2021 Feb 01

Hi Thomas,

Is it possible to join a parameterized CDS view directly with another table A , and pass the value from table A as input to parameterized CDS view?

Appreciate your feedback!

Thank

Siva

former_member184367 · ‎2015 Jan 19

Hi Giri,

1. I am not sure about calling the CDS with input parameters inside AMDP. But according to me we can consume the CDS inside the AMDP using the open sql statement.

SELECT * FROM ycds_input_params( customer_name = 'ABC' ) INTO TABLE @DATA(lt_result)

2. Please check following code. In this code the view named ycds_view_input_params is view with input parameters. That view is consumed in another CDS view named ycds_consume_param.

define view ycds_consume_param as select from ycds_View_input_params( customer_name : 'ABC' ) as vp

{

vp.param_customer_name

}

define view ycds_View_input_params

with parameters customer_name : abap.char(20)

as select

from snwd_so as so

join snwd_bpa as bpa

on bpa.node_key = so.buyer_guid

{

key so.so_id as order_id,

$parameters.customer_name as param_customer_name,

case

when bpa.company_name = $parameters.customer_name

then 'Found it!'

else 'Not found'

end as found_customer

}

where bpa.company_name = $parameters.customer_name

Hope this will help you.

Regards,

Sneha

amol_samte · ‎2015 Jan 19

Hi Sneha,

I have doubt regarding your first point.

Can you please share me some documents to call CDS inside AMDP using open SQL statement.

Regards,

Amol

amol_samte · ‎2015 Jan 19

Hello Giri,

Yes We can call CDS view with ip parameter inside AMDP.

E.g.

As you have alredy CDS view with ip parameter.

METHOD GET_CDS_DATA BY DATABASE PROCEDURE

FOR HDB

LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY

USING ZCDS_VIEW. <-- CDS view name i/p parameter.

et_details = select flight_id, connid from ZCDS_VIEW(p_carrid => 'AC') ;

ENDMETHOD.

Cheers,

Amol

Former Member · ‎2016 Jun 13

Hi ,

I have created CDS view with Input parameter and tried to consume in AMDP as below

e_result = SELECT field1 field2 FROM ZXV_PERSON(CDS VIEW not SQLVIEW NAME) ( P_STRING =>:iv_string1 );

but i am getting error in USING clause as "view is invalid only transparent tables, views or database procedure can be specified "

I have cross verified that CDS view exist in abap catalog (using se11 tcode)

any solution ?

thank you!

Regards

Vinuta Hegde

Former Member · ‎2015 May 15

Hello,

what should be used in USING clause of method implementation of an AMDP? View name or sqlViewName?

When i use SqlviewName there is no error but when i use select on this SqlViewName it pops out error "view/table" not defined in schema.

If i use ViewName it says "... view is invalid. Only transparent tables/ views or database procedures can be specified"

I am on SPS9.

Thanks

-ashish

amol_samte · ‎2015 May 15

Hello Kumar,

The USING clause is used for accessing DB tables inside AMDP.

For accessing view you can directly write a query on view inside AMDP.

e.g.

METHOD get_bukrs BY DATABASE PROCEDURE

FOR HDB

LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY.

et_bukrs = SELECT * FROM "_SYS_BIC"."schema.dev.view/CV_COMPCODE"

WHERE mandt = :ip_mandt;

-Amol

Former Member · ‎2015 May 19

Thanks Amol for your input.

i could resolve this issue myself when i installed Revison 95 of studio.

Regards,

ashish

san_syn · ‎2021 Aug 23

Can you Provide an Example for Declaring the CDS View With Input parameter in AMDP

Without Parameter It is working .