Skip to Content

Need help on generation of time series using Calculation view

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 05, 2016 at 07:21 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

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