Fw: views

On Wed, 17 Apr 2002 02:27:08 +0900
Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> wrote:

explain select * from vtb_timecard;

drop view vtb_timecard;
create view vtb_timecard as
select
s.adjperiod, s.adjusername, s.adjperiodno, s.payperiodno,
s.ppstartdt,
s.timelocid, s.supvpos,
s.employeeid,
s.timecardid,
s.programid, s.timecode, s.programno,
s.shiftdiff,
s.signed, s.approved, s.employeeno,
sum(case when d.daynumber = 1 then d.hours else 0 end) as h1,
sum(case when d.daynumber = 2 then d.hours else 0 end) as h2,
sum(case when d.daynumber = 3 then d.hours else 0 end) as h3,
sum(case when d.daynumber = 4 then d.hours else 0 end) as h4,
sum(case when d.daynumber = 5 then d.hours else 0 end) as h5,
sum(case when d.daynumber = 6 then d.hours else 0 end) as h6,
sum(case when d.daynumber = 7 then d.hours else 0 end) as h7,
sum(case when d.daynumber = 8 then d.hours else 0 end) as h8,
sum(case when d.daynumber = 9 then d.hours else 0 end) as h9,
sum(case when d.daynumber = 10 then d.hours else 0 end) as h10,
sum(case when d.daynumber = 11 then d.hours else 0 end) as h11,
sum(case when d.daynumber = 12 then d.hours else 0 end) as h12,
sum(case when d.daynumber = 13 then d.hours else 0 end) as h13,
sum(case when d.daynumber = 14 then d.hours else 0 end) as h14,
sum(d.hours) as totall
from
(select distinct coalesce(td.adjperiod,0) as adjperiod,
case when td.adjperiod > 0 then au.username
else null
end as adjusername,
td.enterbyid, pp2.payperiodno as adjperiodno,
posit.timelocid,
posit.supvpos, tc.approved, tc.signed, tc.employeeid,
e.employeeno, pp.payperiodno, pp.ppstartdt, pp.ppenddt,
td.timecardid, td.programid, p.programno, td.timecodeid,
tcd.timecode, td.shiftdiff
from
timedetail td
join timecode tcd on td.timecodeid = tcd.timecodeid
join timecard tc on td.timecardid = tc.timecardid
join payperiod pp on tc.payperiodid = pp.payperiodid
join program p on td.programid = p.programid
join posemp pe on tc.employeeid = pe.employeeid
join posit on pe.positid = posit.positid
join employee e on tc.employeeid = e.employeeid
left outer join payperiod pp2 on td.adjperiod = pp2.payperiodid
join userpass au on td.enterbyid = au.employeeid
where pp.ppstartdt >= pe.posempeffdate and
(pp.ppstartdt <= pe.posemptermdate or posemptermdate is null)
) as s left outer join
(select daynumber, coalesce(adjperiod,0) as adjperiod,
enterbyid, timecardid, timecodeid, programid,
shiftdiff, sum(hours) as hours
from timedetail
group by daynumber, coalesce(adjperiod,0), enterbyid,
timecardid, timecodeid, programid, shiftdiff
) as d
on
s.timecardid = d.timecardid and
s.adjperiod = d.adjperiod and
s.programid = d.programid and
s.timecodeid = d.timecodeid and
s.enterbyid = d.enterbyid and
s.shiftdiff = d.shiftdiff
group by
d.daynumber, s.adjperiod, s.adjusername,
s.adjperiodno, s.payperiodno, s.ppstartdt,
s.timelocid, s.supvpos, s.employeeid,
s.timecardid, s.programid, s.timecode,
s.programno, s.shiftdiff, s.signed, s.approved,
s.employeeno
;

>
> drop view vtb_timecard;
> create view vtb_timecard as
> select s.adjperiod, s.adjusername, s.adjperiodno, s.payperiodno, s.ppstartdt,
> s.timelocid, s.supvpos,
> s.employeeid,
> s.timecardid,
> s.programid, s.timecode, s.programno,
> s.shiftdiff,
> s.signed, s.approved, s.employeeno,
> sum(d1.hours) as h1, sum(d2.hours) as h2, sum(d3.hours) as h3,
> sum(d4.hours) as h4, sum(d5.hours) as h5, sum(d6.hours) as h6,
> sum(d7.hours) as h7, sum(d8.hours) as h8, sum(d9.hours) as h9,
> sum(d10.hours) as h10, sum(d11.hours) as h11, sum(d12.hours) as h12,
> sum(d13.hours) as h13, sum(d14.hours) as h14, sum(progcode.hours) as total
>
> from
>
> (
> select distinct coalesce(td.adjperiod,0) as adjperiod,
> case when td.adjperiod > 0 then au.username else null end as adjusername,
> td.enterbyid, pp2.payperiodno as adjperiodno,
> posit.timelocid,
> posit.supvpos, tc.approved, tc.signed, tc.employeeid,
> e.employeeno, pp.payperiodno, pp.ppstartdt, pp.ppenddt,
> td.timecardid, td.programid, p.programno, td.timecodeid, tcd.timecode ,
> td.shiftdiff
> from timedetail td
> join timecode tcd on td.timecodeid = tcd.timecodeid
> join timecard tc on td.timecardid = tc.timecardid
> join payperiod pp on tc.payperiodid = pp.payperiodid
> join program p on td.programid = p.programid
> join posemp pe on tc.employeeid = pe.employeeid
> join posit on pe.positid = posit.positid
> join employee e on tc.employeeid = e.employeeid
> left outer join payperiod pp2 on td.adjperiod = pp2.payperiodid
> join userpass au on td.enterbyid = au.employeeid
> where pp.ppstartdt >= pe.posempeffdate and
> (pp.ppstartdt <= pe.posemptermdate or posemptermdate is null)
> ) as s left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 1
> group by coalesce(adjperiod,0), enterbyid, timecardid, timecodeid, programid, shiftdiff) as d1
>
> on s.timecardid = d1.timecardid and
> s.adjperiod = d1.adjperiod and
> s.programid = d1.programid
> and s.timecodeid=d1.timecodeid and
> s.enterbyid = d1.enterbyid
> and s.shiftdiff = d1.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 2
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d2
>
> on s.timecardid = d2.timecardid and
> s.adjperiod = d2.adjperiod and
> s.programid = d2.programid and
> s.timecodeid=d2.timecodeid and
> s.enterbyid = d2.enterbyid and
> s.shiftdiff = d2.shiftdiff
>
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 3
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d3
>
> on s.timecardid = d3.timecardid and
> s.adjperiod = d3.adjperiod and
> S.programid = d3.programid and
> s.timecodeid=d3.timecodeid and
> s.enterbyid = d3.enterbyid and
> s.shiftdiff = d3.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 4
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d4
>
> on s.timecardid = d4.timecardid and
> s.adjperiod = d4.adjperiod and
> s.programid = d4.programid
> and s.timecodeid = d4.timecodeid and
> s.enterbyid = d4.enterbyid and
> s.shiftdiff = d4.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 5
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d5
>
> on s.timecardid = d5.timecardid and
> s.adjperiod = d5.adjperiod and
> s.programid = d5.programid and
> s.timecodeid=d5.timecodeid and
> s.enterbyid = d5.enterbyid and
> s.shiftdiff = d5.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 6
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d6
>
> on s.timecardid = d6.timecardid and
> s.adjperiod = d6.adjperiod and
> s.programid = d6.programid and
> s.timecodeid=d6.timecodeid and
> s.enterbyid = d6.enterbyid and
> s.shiftdiff = d6.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 7
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d7
>
> on s.timecardid = d7.timecardid and
> s.adjperiod = d7.adjperiod and
> s.programid = d7.programid and
> s.timecodeid=d7.timecodeid and
> s.shiftdiff = d7.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 8
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d8
>
> on s.timecardid = d8.timecardid and
> s.adjperiod = d8.adjperiod and
> s.programid = d8.programid and
> s.timecodeid=d8.timecodeid and
> s.enterbyid = d8.enterbyid and
> s.shiftdiff = d8.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 9
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d9
>
> on s.timecardid = d9.timecardid and
> s.adjperiod = d9.adjperiod and
> s.programid = d9.programid and
> s.timecodeid=d9.timecodeid and
> s.enterbyid = d9.enterbyid and
> s.shiftdiff = d9.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 10
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d10
>
> on s.timecardid = d10.timecardid and
> s.adjperiod = d10.adjperiod and
> s.programid = d10.programid and
> s.timecodeid=d10.timecodeid and
> s.enterbyid = d10.enterbyid and
> s.shiftdiff = d10.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 11
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d11
>
> on s.timecardid = d11.timecardid and
> s.adjperiod = d11.adjperiod and
> s.programid = d11.programid and
> s.timecodeid=d11.timecodeid and
> s.enterbyid = d11.enterbyid and
> s.shiftdiff = d11.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 12
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d12
>
> on s.timecardid = d12.timecardid and
> s.adjperiod = d12.adjperiod and
> s.programid = d12.programid and
> s.timecodeid=d12.timecodeid and
> s.enterbyid = d12.enterbyid and
> s.shiftdiff = d12.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 13
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d13
>
> on s.timecardid = d13.timecardid and
> s.adjperiod = d13.adjperiod and
> s.programid = d13.programid and
> s.timecodeid=d13.timecodeid and
> s.enterbyid = d13.enterbyid and
> s.shiftdiff = d13.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail where daynumber = 14
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as d14
>
> on s.timecardid = d14.timecardid and
> s.adjperiod = d14.adjperiod and
> s.programid = d14.programid and
> s.timecodeid=d14.timecodeid and
> s.enterbyid = d14.enterbyid and
> s.shiftdiff = d14.shiftdiff
> left outer join
>
> (select coalesce(adjperiod,0) as adjperiod, enterbyid, timecardid, timecodeid, programid, shiftdiff, sum(hours) as hours from timedetail
> group by coalesce(adjperiod,0) , enterbyid, timecardid, timecodeid, programid, shiftdiff) as progcode
>
> on s.timecardid = progcode.timecardid and
> s.programid = progcode.programid and
> s.adjperiod = progcode.adjperiod and
> s.timecodeid = progcode.timecodeid and
> s.enterbyid = progcode.enterbyid and
> s.shiftdiff = progcode.shiftdiff
>
> group by s.adjperiod, s.adjusername, s.adjperiodno, s.payperiodno, s.ppstartdt, s.timelocid,
> s.supvpos,
> s.employeeid, s.timecardid,
> s.programid, s.timecode, s.programno,
> s.shiftdiff,
> s.signed, s.approved, s.employeeno
> ;
>

Regards,
Masaru Sugawara