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: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
Add comment