Fw: views

On Wed, 17 Apr 2002 02:27:08 +0900
Masaru Sugawara <rk73@sea.plala.or.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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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
hoursfrom 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