How to create an input parameter to pass another corresponding value?

[former_member197081]

Hi Everyone,

I want to create an Input parameter in a way which will prompt the user to enter the value in some predefined description, but in back end it should pass the ID of that filed. I have a field call ERP System; the ERP system can be identified from ID or the name. Currently the users are entering the value using the ERP name. But to restrict the data from table used in view, based on ERP, I am joining using the ERP ID (as the fact and dimension tables doesn't have ERP name).

So my question is, if it is possible to accept ERP name in the front end and filter the table data based on ERP ID by passing the ERP ID internally?

I have WebI reports running on this view, so if I can achieve this from WebI also a fesible solution for me.

Note: I have one ERP Table, which stores the ERP Name and ERP ID column.

[pfefferf · Accepted solution]

One option to reach this is to use a second (hidden) parameter which parameter type is "Derived from procedure/scalar function".

So you have two paramters:

• Your original parameter in which the user enters the ERP name.
• A second (hidden) parameter which uses a stored procedure or scalar function to determine the ERP ID from the entered ERP name.

Following a dummy example which uses a stored procedure which returns an "id" dependent on a specific text (in that example the values are hard coded but can of course be determined out of a table).

Procedure (consider that the output parameter must also be a string, if another type is required, then the conversion has to be done in the view):

PROCEDURE "MISC"."test.misc.procedures::P_DERIVED_P_MAP_VALUE" ( in i_text nvarchar(100), out e_id nvarchar(4) ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA <default_schema_name>
	READS SQL DATA AS
BEGIN
	if :i_text = 'test01' then
	  e_id := '1';
	elseif :i_text = 'test02' then
	  e_id := '2';
	elseif :i_text = 'test03' then
	  e_id := '3';
	else
	  e_id := '9999';
	end if;
END;

ID Parameter:
This is the (hidden) parameter which receives the ID determined by the procedure. You have to map your first "name" parameter to the input parameter of the procedure.

Regards,
Florian

[former_member197081 · Accepted solution]

Hi Florian,

That's certainly a smart way to achieve it. I tried creating below procedure to covert the ERP Name to ID. But I am getting 2 result sets when i am calling the PROCEDURE.

After calling the PROCEDURE, I am getting the correct result in one tab and NULL in other

Regards,

Deba

[pfefferf · Accepted solution]

In the procedure you are not setting the value of the output parameter IP_RDSOURCEID. Therefore the output parameter is null (indicated by the question mark in the result).

[former_member197081 · Accepted solution]

I am able to get one result set now. But when I am trying to access the Input parameter using a calculated column or putting in an filter, I am getting below error

I tried creating the procedure in sys_bic schema as well. Also, in the procedure I hardcoded come value but the input parameter is not able to pick it. Please help!!

[pfefferf · Accepted solution]

Show more details about your calculation view (how you set up your parameters, how you have used the parameter in the calculated column).

[former_member197081 · Accepted solution]

Below is the calculated column expression and error

below is the filter expression and error

I tried removing the first input parameter where the user are expected to put the erp name and hard coded on erp name in PROCEDURE to see if it is picking the result, but no luck.

I tried "GRANT EXECUTE ON SCHEMA", but I don't have the access for it.

[former_member197081 · Accepted solution]

Bellow is the select query in procedure

select SOURCENUMID into IP_RDSOURCEID from "MISC"."SOURCE" where LONGDESC = 'SAP ERP';

[pfefferf · Accepted solution]

Above in the procedure the ERP ID is an integer. In the calculated column you try to use it as a string (nvarchar(10) w/o a conversion. You should also enclose the parameter name with the dollar signs into single quotes.

[former_member197081 · Accepted solution]

1. ERP ID in table is Integer, but in PROCEDURE I am reading it into a nvarchar(10) (output parameter), so I did not use any type conversion.
2. Now instead of reading the erp id, I changed the procedure to read the erp name (which is nvarchar) to see if the Input parameter can read the data. This case is also not working. I am getting the same type mismatch error.
3. You should also enclose the parameter name with the dollar signs into single quotes. I tried '$$RDSOURCEID$$' and it is returning empty string.

I am not sure what I am missing here !!

[former_member197081 · Accepted solution]

i just hard coded one string to the output parameter (IP_RDSOURCEID = 'hi') in the procedure. I am able to retrieve this in the input parameter using '$$RDSOURCEID$$'.

But when I am writing the query:

select LONGDESC into IP_SOURCEID from "MISC"."RDSOURCE" where LONGDESC = 'ABC';

It is not returning any data. I am able to call the procedure and get value in sql console.

Am I writing the wrong select statement ?

[pfefferf · Accepted solution]

Looks fine. Did you check that the statement produces a result?

[former_member197081 · Accepted solution]

It does. Even when I am doing Select 'Hello' into IP_RDSOURCEID from dummy; the IP returning value. It is when I am selecting from an actual table it is returning nothing.

Do you think I need to provide any authorization or anything ?

[former_member197081 · Accepted solution]

I created a sample table in Sys_bic, and changed the procedure to point to the new table and now everything works as intended. But the problem is I can't use it from sys_bic, I have to use it from the original schema. Please help me with what type of authorization I need for this.

[pfefferf · Accepted solution]

Have you done an authorization trace to check if it is authorization related and what authorization is missing. You find the details about that in the HANA troubleshooting guide.

[former_member197081 · Accepted solution]

Hi Florian,

Sorry for replying late. The issue was really weird, when I created the same table in sys_bic schema then also it did not work. So I created a new table in its own schema and loaded the records from the existing schema, it worked. Though I could not figure out the reason of this.

Regards,

Deba