Dear SCN Members,@lars.breddemann,
Following is the method which I am using for generating time series for the two dates of different available in a table i.e., 'Start Date' and 'End Date' columns.
- Created a calculation view (Graphical) to get the maximum of End Date column using measure aggregate 'Max'. i.e., column "MAX_DATE"
- Created a calculation view to get the minimum of Start Date column using measure aggregate 'Min'. i.e., "MIN_DATE"
- Created a calculation view (SQL Script) as follows:
/********* Begin Procedure Script ************/
BEGIN
temp_1 = SELECT"MIN_DATE"FROM"_SYS_BIC"."CALC_MIN";
temp_2 = SELECT"MAX_DATE"FROM"_SYS_BIC"."CALC_MAX";
var_out = SELECT "GENERATED_PERIOD_START","GENERATED_PERIOD_END" FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 60 SECOND', :temp_1,:temp_2);
END;
/********* End Procedure Script ************/
Added columns "GENERATED_PERIOD_START","GENERATED_PERIOD_END" as TIMESTAMP data type.
But during activation, received an error as follows:
TABLE type is incompatible with TIMESTAMP type: line 10 col 27 (at pos 581)
Please suggest if I have missed anything or it can be achieved by other steps in calculation view.
Sincerely,
Nachappa S
Hello Nachappa,
the problem is that you pass the table typed variables :temp_1 and :temp_2 to the SERIES_GENERATE_TIMESTAMP function, but scalar values are expected. You have to get the scalar values and pass it to the function.
Like following for instance (I used only local declared variables w/o selection from another view, but to get the single value from the view is not really a problem :-)):
DELCARE temp_1 date := to_date('2016-01-01'); DECLARE temp_2 date := to_date('2016-01-02'); var_out = SELECT "GENERATED_PERIOD_START","GENERATED_PERIOD_END" FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 60 SECOND', :temp_1, :temp_2);
Regards,
Florian
PS: To avoid future migration/reimplementation efforts I would recommend that you use a Table Function instead of a Scripted Calculation View for your implementation.
Thank you Florian for suggestions.
In my case "but to get the single value from the view is not really a problem", Please suggest.
I have the values coming from calculation views "CALC_MIN" and "CALC_MAX"
SELECT "MIN_DATE" FROM "_SYS_BIC"."CALC_MIN";
SELECT "MAX_DATE" FROM "_SYS_BIC"."CALC_MIN";
Sincerely,
Nachappa S
Hi Florian,
Thank you!. I could figure out "but to get the single value from the view is not really a problem :-)"
DECLARE temp_2 date;
DECLARE temp_1 date;
SELECT "MIN_DATE" INTO temp_1 FROM "_SYS_BIC"."CALC_MIN";
SELECT "MAX_DATE" INTO temp_2 FROM "_SYS_BIC"."CALC_MAX";