select rpc.noide
from KPER.personnephysiqueprivee pp
inner join KCTR.rolepersonnecontrat rpc on rpc.noide = pp.noide 
                                      and rpc.dat1varol is null
                                      and rpc.datfineffrol is null
where dat1vapppriv is null
and pnasi  = 'Charlie'
and rpc.codsttas = 'SSCR'
group by rpc.noide;