Myujth

I have a script to populate previous(2016), current(2017) and complete next year(2018). The script is intended to be run initially to populate table. It can be run only once since it populates prior dates. How do I populate future dates (2019)?

insert into my_date
SELECT TO_NUMBER (TO_CHAR (mydate, 'yyyymmdd')) AS my_date_id,
 mydate AS datetime_start,
 mydate + 1 - 1/86400 AS datetime_end,
 TO_CHAR (mydate, 'dd-MON-yyyy') AS date_value,
 TO_NUMBER (TO_CHAR (mydate, 'D')) AS day_of_week,
 TO_CHAR (mydate, 'Day') AS day_of_week_name,
 TO_CHAR (mydate, 'DY') AS day_of_week_name_short,
 TO_NUMBER (TO_CHAR (mydate, 'DD')) AS day_of_month,
 TRUNC (mydate) - TRUNC (mydate, 'Q') + 1 AS day_of_quarter,
 TO_NUMBER (TO_CHAR (mydate, 'DDD')) AS day_of_year, 
 CASE WHEN TO_NUMBER (TO_CHAR (mydate, 'D')) IN (1, 7) THEN 1
 ELSE 0
 END AS weekend_flag,
 TO_NUMBER (TO_CHAR (mydate, 'W')) AS week_in_month,
 TO_NUMBER (TO_CHAR (mydate, 'WW')) AS week_in_year,
 TRUNC(mydate, 'w') AS week_start_date,
 TRUNC(mydate, 'w') + 7 - 1/86400 AS week_end_date,
 TO_CHAR (mydate, 'MM') AS month_value,
 TO_CHAR (mydate, 'Month') AS month_name,
 TO_CHAR (mydate, 'MON') AS month_name_short,
 TRUNC (mydate, 'mm') AS month_start_date,
 LAST_DAY (TRUNC (mydate, 'mm')) + 1 - 1/86400 AS month_end_date,
 TO_NUMBER ( TO_CHAR( LAST_DAY (TRUNC (mydate, 'mm')), 'DD')) AS days_in_month,
 CASE WHEN mydate = LAST_DAY (TRUNC (mydate, 'mm')) THEN 1
 ELSE 0
 END AS last_day_of_month_flag, 
 TO_CHAR (mydate, 'yyyy') AS year_value,
 'YR' || TO_CHAR (mydate, 'yyyy') AS year_name,
 'YR' || TO_CHAR (mydate, 'yy') AS year_name_short,
 TRUNC (mydate, 'Y') AS year_start_date,
 ADD_MONTHS (TRUNC (mydate, 'Y'), 12) - 1/86400 AS year_end_date,
 ADD_MONTHS (TRUNC (mydate, 'Y'), 12) - TRUNC (mydate, 'Y') AS days_in_year
 FROM ( SELECT TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy') - 1 + LEVEL AS mydate
 FROM dual
 CONNECT BY LEVEL <= (SELECT TRUNC (ADD_MONTHS (SYSDATE, 24), 'yy')
 - TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy')
 FROM DUAL
 )
 );

A change will be required within FROM clause to avoid existing records. How do I achieve that?

A pipelined function might help you here.

1. Create a table type that will match the results your function will return.


2. Create a function which returns exactly the dates you want. It can run queries to make sure the date isn't already in your table, is within your desired date range, etc.


3. Return the values one by one stopping once you hit your criteria.


4. Select from the function using the TABLE() function to turn the results into a table you can query. Use the OBJECT_VALUE to access the actual value being returned (since it doesn't really have a column name).

create or replace type date_tbl_t as table of date;
/
create or replace function all_dates ( max_year_in in integer ) 
 return date_tbl_t 
 pipelined 
as
 date_l date;
 offset_l pls_integer := 0;
 year_l integer;
begin 
 if date_l is null
 then 
 date_l := sysdate;
 end if;
 year_l := extract ( year from date_l );
 while year_l <= max_year_in 
 loop
 pipe row(date_l);
 date_l := date_l + 1;
 year_l := extract ( year from date_l );
 end loop;
 return;
end all_dates;
/
select 
 to_char(x.object_value, 'yyyymmdd') as my_date_id,
 x.object_value as datetime_start
from table ( all_dates (2019) ) x;
/