Need help on generation of time series using Calculation view

[darshansomaiah1]

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

[pfefferf · Accepted solution]

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.

[darshansomaiah1 · Accepted solution]

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

[darshansomaiah1 · Accepted solution]

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";