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 a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.