Example 6: Use Input Parameters with Derived From Procedure based on another input parameter
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.