select
mpc_clnpsdep.code spsdep,
mpc_prprof.code sprof,
mpc_clnpersons.code spers,
companies.name scompany
from
mpc_clnpspfm
left join companies on companies.rn = mpc_clnpspfm.company
left join mpc_clnpsdep on mpc_clnpsdep.rn = mpc_clnpspfm.postrn
left join mpc_clnpersons on mpc_clnpersons.rn = mpc_clnpspfm.persrn
left join mpc_prprof on mpc_prprof.rn = mpc_clnpspfm.prprof
where mpc_clnpsdep.postrn in
(

select postrn /*, count(*)*/ from
(
select mpc_clnpsdep.postrn, mpc_clnpspfm.prprof
from mpc_clnpspfm, mpc_clnpsdep
where mpc_clnpsdep.rn = mpc_clnpspfm.postrn
--and mpc_clnpspfm.prprof is not null and mpc_clnpspfm.prprof <> 214705
group by mpc_clnpsdep.postrn, mpc_clnpspfm.prprof
)
group by postrn
having count(*) > 1

)
order by
mpc_clnpsdep.code,
mpc_prprof.code,
mpc_clnpersons.code,
companies.name

-- Add/modify columns
alter table MPC_CLNPOSTS add PRPROF number(17);
-- Create/Recreate primary, unique and foreign key constraints
alter table MPC_CLNPOSTS
add constraint C_MPC_CLNPOSTS_PRPROF_FK foreign key (PRPROF)
references mpc_prprof (RN);

--select mpc_clnposts.*,
update mpc_clnposts set prprof =
(
select min(pfm.prprof) from mpc_clnpspfm pfm
where pfm.postrn =
(select min(rn) from mpc_clnpsdep psdep where psdep.postrn = mpc_clnposts.rn)
and pfm.prprof is not null
)
--from mpc_clnposts