on 05-03-2013 1:32 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.