on 03-08-2018 12:08 PM
Hi Experts,
I have created a stored procedure which is calling a Calculation view.I need to pass multiple input parameter value but i am not able to do so. I have checked all the blog regarding this but everywhere it is shown by passing the value itself. I want to pass the input parameter not value.Below is my Code snippets.
-- IP_MATERIAL=000000000067064914 OR 000000000062040102;(Input parameter)
IF (LENGTH(:IP_MATERIAL) <= 19 ) THEN
VAR_IP_MATERIAL := REPLACE(''''||:IP_MATERIAL||'''', ' OR ', ''',''');
ELSE VAR_IP_MATERIAL := REPLACE(''''||:IP_MATERIAL||'''', ' OR ', ''''',''''');
END IF;
-- VAR_IP_MATERIAL= '000000000067064914'','' 000000000062040102' (converted value of input parameter)
TT_AK=
SELECT MANDT,MATNR,WERKS,C_DATE,LGORT, SUM(IFNULL("LABST",0)) AS C_OPENING_STOCK, SUM(IFNULL("LABST",0)) AS LABST FROM "_SYS_BIC"."A. CA_PS_MARD" ( PLACEHOLDER."$IP_PLANT$" => :VAR_PLANT, PLACEHOLDER."$IP_MATERIAL$" => :IP_MATERIAL, PLACEHOLDER."$IP_DATE_TO_RUN_REPORT$" => :IP_CURR_DATE, PLACEHOLDER."$IP_STORAGE_LOC$" => :VAR_LGORT ) GROUP BY MANDT,MATNR,WERKS,C_DATE,LGORT;
Through above code i am able to pass single value as input parmeter but if IP_MATERIAL is passed like 000000000067064914 OR 000000000062040102 that is i am passing two material and doing some string workaround i am finally passing it as
'000000000067064914'','' 000000000062040102' then it is throwing error.
Earlier i was using the Dynamic SQL inside SP which is still working fine.But i want to remove the dynamic SQL part.
Dynamic SQL Code(Earlier):
EXEC 'INSERT INTO #OPENING_STOCK SELECT MANDT,MATNR,WERKS,C_DATE,LGORT,SUM(IFNULL("LABST",0)) AS C_OPENING_STOCK, SUM(IFNULL("LABST",0)) AS LABST FROM "_SYS_BIC"."A.CA_PS_MARD" ( PLACEHOLDER."$IP_PLANT$" => '||:VAR_PLANT||', PLACEHOLDER."$IP_MATERIAL$" => '||:VAR_IP_MATERIAL||', PLACEHOLDER."$IP_DATE_TO_RUN_REPORT$" => '''||:IP_CURR_DATE||''', PLACEHOLDER."$IP_STORAGE_LOC$" => '||:VAR_LGORT||' ) GROUP BY MANDT,MATNR,WERKS,C_DATE,LGORT';
This is working perfectly.
How should i pass the value, i even tried with different combination of VAR_IP_MATERIAL like '''000000000062040102'',''000000000067064914''' but all in vain ,or do i need to do some code change while calling stored procedure .....Please help me with this.
I am using SAP HANA 1.0 SPS 11.
Regards,
Anuj Kumar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.