Skip to Content
-1

Output not coming as expected

Jul 14, 2017 at 07:01 PM

94

avatar image

error.png - This is the output which I am getting after running the procedure.

expected.png - This is the kind of output I am looking for. Just that It should start from 2010-01-01 instead of 2017.

Below is my current code:-

create procedure bhavya.zz_get_series()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

AS

LV_START_DATE date := '2010-01-01' ;

LV_END_DATE date := current_date ;

LV_WEEK_END NVARCHAR(10) := 'FRIDAY';

lv_report_date Date ;

Begin TT_SERIES = (SELECT GENERATED_PERIOD_START AS REPORT_DATE, week(add_days(generated_period_start , 2)) as report_week, current_date as week_end --added ABHOOT FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, ADD_DAYS(coalesce(current_date,:LV_END_DATE), 1)));

TT_WEEK_END = select report_week, max(report_date) as week_end from :TT_SERIES group by report_week ;

insert into "BHAVYA"."AFS_BASE.KPI.TABLES::DB_WEEK_SERIES1" (REPORT_DATE,REPORT_WEEK,WEEK_END) select S.report_date, w.report_week, w.week_end from :TT_SERIES S left join :TT_WEEK_END W on w.report_week = s.report_week;

end;

call bhavya.zz_get_series

Tried doing different stuff but could not find how to do it. Any help is appreciated. Thanks.

error.png (39.0 kB)
expected.png (191.4 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abdul Mushfiq Jul 18, 2017 at 05:54 AM
0

where will I get SERIES_GENERATE_DATE ?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Abdul,

SERIES_GENERATE_DATE is already there in my system and it's working properly. I do not think this is the problem here.

0