This is really, but really strange! I created the following stored function to subtract from a date just working days (avoiding week ends, basically)
create or replace function minusLaborDays(endDate Date, days Number)
return Date
as
day_of_week Number;
resultD Date;
i number;
begin
i:=0;
resultD := endDate;
while i<days
loop
resultD := resultD - 1;
day_of_week := to_number(to_char(resultD,'D'));
if(day_of_week <= 5)
then
i := i+1;
end if;
end loop;
return resultD;
end;
Now, I want to select a date (along with other values) from a table and insert it into another table.
Here's the sql:
insert /*+ append */ into TABLE2 (c1,c2,c3,my_date)
(select distinct E.c1, E.c2, E.c3, minusLaborDays(trunc(E.end_date),2) my_date from TABLE1 E);
Now, with this code I successfully insert 1900 rows.
If I remove the call to my function, so I simply insert the date as it is...I insert 1903 rows!
Why is that?