Input parameter based on procedure of type Date

Use case: The user is asked to enter a date. If nothing is specified then it should be default to first day of the current month else user specified value should be used to filter the data, using Graphical calculation view.

If you are thinking to do by using Input parameter with “Derived from Procedure/Scalar function”
then you are almost there with just 2 hurdles to cross.

For this demonstration I’m using the below table structure for which the field
DOJ is of type DATE on which the input parameter will be used.

Sample Data in the table:

Create a procedure which returns the date (first day of current month).

CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY DATE)

LANGUAGE SQLSCRIPT

AS

BEGIN

---- Write the logic based on business requirement.

---- This logic gives you the first day of the current month as date

SELECT ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)

INTO FIRST_DAY

FROM DUMMY;

END

;

Call the procedure to see the output:

CALL RAJ.FIRST_DAY(?);

In graphical calculation view, Create Input parameter which is based on above procedure.

Now you will come across the first hurdle 

Error message is - Procedure must have scalar parameter of type String, which is because of the product limitation.

The input parameter is based on Date and there is a product limitation to use string type only.

Fortunately the dates are given in single quotes in a SQL query, hence this should not stop us to go ahead.

Let us go back to Procedure and change the

out parameter type from DATE to VARCHAR(10) and

convert the date output to string using TO_CHAR.

DROP PROCEDURE RAJ.FIRST_DAY;

CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

BEGIN

    SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1))

    INTO FIRST_DAY FROM DUMMY;

END

;

Now in the input parameter, give the procedure name. This time it should accept

without error message. Hurdle number 1 crossed

Apply the input parameter on your required date field in Graphical calculation view (in my case the field is DOJ).

Validate and Activate the view.

On Data Preview you see Input Parameter output value is not passed. Here comes the hurdle number 2 

Somehow the output of procedure is not getting populated correctly. Could not catch the actual reason for this.

Generally when Attribute/Analytic/Calculation view is activated, a column view of the same is placed in _SYS_BIC from which the system access.

So I changed the schema name in procedure to _SYS_BIC.

The only change this time will do is change the schema name to _SYS_BIC.

DROP PROCEDURE _SYS_BIC.FIRST_DAY;

CREATE PROCEDURE _SYS_BIC.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

BEGIN

SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)) INTO FIRST_DAY FROM DUMMY;

END;

Check the output of your procedure by CALL _SYS_BIC.FIRST_DAY(?);

Now modify the Input parameter in calculation view to point to schema _SYS_BIC.

Activate the view and do the data preview.

This time you will see the output of the procedure being populated. Hurdle number 2 crossed

Output of view based on input parameter value returned from procedure:

Getting the required result. Now again do the data preview and give the date we want,

Data is fetching as expected.