Another correction from Cristiano to be done........

Dear All,
 
the previous version of the view would suppress the number of candidate. The new version (found below) contains this information.
Best regards
 
Cristiano
 
 
 
 
create or replace view opera.vw_local_cs_candidates as
with csdata as
(
select idev, idbk, idpl, cand, idcand, idz, sd, idmu,
tb_mipmicrotracks.grains, posx, posy, refz as posz, slopex, slopey from
(select idev, idbk, tb_zones.id_plate as idpl, cand, idcand, idz, sd, idmu,
refz from
(select idev, idbk, cand, idcand, idz, sd, idmu, decode(sd,1,
tb_views.upz,2, tb_views.downz) as refz from
(select ca.id_event as idev, ca.id_eventbrick as idbk, ca.candidate as cand,
cb.id_candidate as idcand, cb.id_zone as idz, cb.side as sd,
cb.id_microtrack as idmu from
(select id_event, id_eventbrick, candidate, id from tb_cs_candidates) ca
inner join tb_cs_candidate_tracks cb on (ca.id_eventbrick = cb.id_eventbrick
and cb.id_candidate = ca.id))
inner join tb_views on (idbk = tb_views.id_eventbrick and idz =
tb_views.id_zone and sd = tb_views.side))
inner join tb_zones on (tb_zones.id_eventbrick = idbk and tb_zones.id =
idz))
inner join tb_mipmicrotracks on (tb_mipmicrotracks.id_eventbrick = idbk and
tb_mipmicrotracks.id_zone = idz and tb_mipmicrotracks.side = sd and
tb_mipmicrotracks.id = idmu)
)
select idev as id_event, idbk as id_cs_eventbrick, idcand, cand, idpl as id_plate,
nvl2(sdprev, 0, sd) as side, nvl2(sdprev, g1 + g2, g1) as grains,
round(nvl2(sdprev, px2, px1),1) as posx, round(nvl2(sdprev, py2, py1),1) as
posy, round(nvl2(sdprev, (px2 - px1) / (pz2 - pz1), sx1),4) as slopex,
round(nvl2(sdprev, (py2 - py1) / (pz2 - pz1), sy1), 4) as slopey from
(
select idev, idbk, idpl, sd,
 lag(sd,1,null) over (partition by idev, idbk, idcand, idpl order by sd) as
sdprev,
 lead(sd,1,null) over (partition by idev, idbk, idcand, idpl order by sd) as
sdnext,
 idcand, cand, grains as g1, posx as px1, posy as py1, posz as pz1, slopex as sx1,
slopey as sy1,
 lag(grains,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as g2,
 lag(posx,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as px2,
 lag(posy,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as py2,
 lag(posz,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as pz2,
 lag(slopex,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as sx2,
 lag(slopey,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as sy2 from csdata
) where ((sd = 2 and sdprev = 1) or (sdprev is null and sdnext is null))