declare
cursor cur_test is
select t.txt_desig,
m.segment_id,
s.code_type_direct,
case
when s.uom_dist_ver_lower = 'FL' then
s.uom_dist_ver_lower || s.val_dist_ver_lower
else
to_char(s.val_dist_ver_lower)
end as val
from nvduser11.en_route_rte t
right join nvduser11.rte_seg s
left join fmap2.segment m
on s.segment_id = m.segment_id on
t.en_route_rte_id = s.en_route_rte_id;
begin
for c in cur_test loop
update fmap2.segment
set first =
(case
when first is null or first = '' then
c.txt_desig
else
to_char(first)
end),
second =
(case
when (first is not null or first != '') and
(second is null or second = '') then
c.txt_desig
else
to_char(second)
end),
third =
(case
when (second is not null or second != '') and
(first is not null or first != '') and (third is null or third = '') then
c.txt_desig
else
to_char(third)
end),
firstdirect =
(case
when first is null or first = '' then
c.code_type_direct
else
to_char(firstdirect)
end),
firstaltitude =
(case
when first is null or first = '' then
c.val
else
to_char(firstaltitude)
end),
seconddirect =
(case
when (first is not null or first != '') and
(second is null or second = '') then
c.code_type_direct
else
to_char(seconddirect)
end),
secondaltitude =
(case
when (first is not null or first != '') and
(second is null or second = '') then
c.val
else
to_char(secondaltitude)
end),
thirddirect =
(case
when (second is not null or second != '') and
(first is not null or first != '') and (third is null or third = '') then
c.code_type_direct
else
to_char(thirddirect)
end),
thirdaltitude =
(case
when (second is not null or second != '') and
(first is not null or first != '') and (third is null or third = '') then
c.val
else
to_char(thirdaltitude)
end)
where segment_id = c.segment_id;
end loop;
commit;
end;