Calling procedure with input parameters in sap hana

[Former Member]

Hi All,

I have written below procedure in HANA and trying to call.

CREATE PROCEDURE "PRICING"."TXN_03_STATS" LANGUAGE SQLSCRIPT AS

vDate_Beg date := '';

VDate_end date := '';

BEGIN

INSERT INTO SEGMENT_STATS ( SEGMENT_ID ,

     REVENUE ,

     PRICE_MEAN ,

     PRICE_STDDEV ,

     TRANSACTION_COUNT,

     CUSTOMER_COUNT ,

     PRODUCT_COUNT )

SELECT

     SEGMENT_NUM ,

     SUM( VLISTPRICE * (1 - DISCOUNT_PCT) * VQUANTITY),

     AVG( VLISTPRICE * (1 - DISCOUNT_PCT) ),

     STDDEV(VLISTPRICE * (1 - DISCOUNT_PCT) ),

     COUNT( 1 ) ,

     COUNT( DISTINCT SOLDTO ) ,

     COUNT( DISTINCT MATERIALSKU)

FROM TXN

WHERE VDATE >= :vDate_Beg

AND VDATE <= :vDate_End

AND IS_LIST_PRICE = 'Y'

GROUP BY SEGMENT_NUM;

END;

I tried to execute below statements without luck.

call "PRICING"."TXN_03_STATS" ('2010-01-01','2013-03-31');

call "PRICING"."TXN_03_STATS" (?,?);

can anyone help me.

Regards,

Venkat

[RichHeilman · Accepted solution]

You have not defined any input parameters.  The two date fields you have defined are intermediate scalar variables, not parameters.

CREATE PROCEDURE "PRICING"."TXN_03_STATS" LANGUAGE SQLSCRIPT AS

vDate_Beg date := '';

VDate_end date := '';

If you want input parameters, you must define them like this.

CREATE PROCEDURE "PRICING"."TXN_03_STATS"( in vDate_Beg date, in vDate_end date )

    LANGUAGE SQLSCRIPT AS

Cheers,

Rich Heilman

[Former Member]

Hi All,

Can anyone please help me in figuring out the CE_PROJECTION logic for something similar to the sql statement above. I am not able to fetch records using filter criteria

Basically i need all rows from my view which has keydate between validfrom and validto

SQL

WHERE validfrom >= :vinput

AND validto <= :vinput

Thanks.

[Former Member]

Hi Venkat,

You are not passing any input parameters in your procedure.So

you need to call  procedure as:

call "PRICING"."TXN_03_STATS" ( );

If you want to pass input parameters in  procedure ,

the procedure should be written like below:

ex:

CREATE PROCEDURE ProcWithResultView (IN d1 date,IN d2 date,

OUT o1 CUSTOMER) LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW ProcView AS

BEGIN

statements ;

END;

Thanks

Basha.

[Former Member]

Hi All,

Yeah got to know after posting and able to execute the procedure successfully.

Thanks for your reply.

Regards,

Venkat