Questions on CDS Views with input parameters

[Former Member]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Thanks Amol for your input.

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

Regards,

ashish

[san_syn]

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

Without Parameter It is working .