2007年12月26日星期三

how to build "cal -3" in oracle?

create or replace
function mon_seq(i number) return dates pipelined
as begin
for j in -6..add_months(sysdate,i)-sysdate+7 loop
pipe row(sysdate + j - (sysdate-trunc(sysdate,'mm'))-1);
end loop;
return;
end;

create or replace view cal3 as
select "MON","TUE","WED","THR","FRI","SAT","SUN" from
(select
lag(column_value,6) over (order by 1) mon,
lag(column_value,5) over (order by 1) tue,
lag(column_value,4) over (order by 1) wed,
lag(column_value,3) over (order by 1) thr,
lag(column_value,2) over (order by 1) fri,
lag(column_value,1) over (order by 1) sat,
column_value sun
from table(mon_seq(3))
)
where to_char(sun,'d')=1 and mon is not null

没有评论: