This example is referenced in my other post about input parameters. Please have a look at the other post to get a better understanding of the context for this example
In the current example we want to fill input parameter
IP_2 based on the return value of procedure
complexLogic. In addition procedure
complexLogic will derive its return value based on the value of input parameter
IP_1.
The current example is pretty artificial but should give you an idea of the options that you have to fill the input parameters. In general, if you can achieve the same logic without using a procedure you might see better performance of your queries.
In the example
IP_1 is filled by a direct input with a date. Depending on the user that is executing the query either 40 days are subtracted from the date or the date is used directly when filling
IP_2.
To reduce the complexity of the model this example starts with the model of
example 2.
a) remove the filter expression of example 2
b) create a procedure with one incoming and one outgoing variable. This procedure contains the logic to calculate
IP_2 based on
IP_1 and based on the user name running the query. The following is an example procedure that would achieve this:
PROCEDURE "inputParameters.db::complexLogic" (
IN IP_1 STRING,
OUT CALCULATEDDATE STRING)
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
IF session_user='PAUL' THEN
CALCULATEDDATE := add_days(:IP_1,-40);
ELSE
CALCULATEDDATE := :IP_1;
END IF;
END;
This procedure will use
IP_1 as an incoming value and subtract 40 days from this date before assigning it to
CALCULATEDDATE if the user running the query is called
PAUL otherwise the value of
IP_1 is directly assigned to
CALCULATEDDATE.
CALCULATEDDATE is the outgoing variable that will fill input parameter
IP_2.
c) create an input parameter
IP_2 that derives its value from this procedure:
d) define a filter using input parameter
IP_2, e.g.
"date"<to_date('$$IP_2$$')
e) map the input parameter of the model to the incoming variable
IP_1 of the procedure. To do so go to node
Semantics and select
Manage Parameter Mapping.
Select as Type:
Procedures/Scalar function for input parameters and map
IP_1 of the procedure to
IP_1 of the view.
This will ensure that the incoming variable of the procedure will be filled with the input parameter at runtime.
Build the view and do a data preview. As value for
IP_1 enter
2018-01-01
If you are running the query with a different name than
PAUL you will see all dates before
2018-01-01:
If you are using a user named
PAUL you will only see dates older than 40 days before the entered input value for
IP_1 which is
2018-01-01:
In this way input parameters that are filled by a procedure at runtime offer you a large flexibility: When designing the procedure you can refer to other input parameters and use additional SQL logic. The logic of the procedure could be reused in multiple Calculation Views when filling the respective input parameters. However, this large flexibility might incur a cost in terms of performance. Therefore, check whether the same functionality is not available also without usage of a procedure.
Click here to navigate back to the context in which this example is discussed. You will also find further examples there.