I was trying to modify an existing query using oracle sql developer 18.0.3 I have a large query
I enclose the query to make it clearer
<entry key="descriptor.pers">
WITH t1 as (select t1ID,
listagg(nvl(cod,'#'), ',')
within group (order by cod) as codes,
listagg(nvl(name,'#'), ',')
within group (order by name) as names,
listagg(nvl(ar,'#'), ',')
within group (order by ar) as ars
from de
group by t1ID)
select
t2.t1ID as t1ID,
t2.nom,
t2.tr as tr, t2.bl as bl, t2.dn as dn, t2.ta as ta,
t2.fi, t2.ff,
t2.INO,
ex.codes as cicod,
ex.names as ciname,
ex.ars as ciorg,
null as ticom,
null as dticom,
t3.nl as nl,
t3.cft,
t2.dcc AS dcc,
t2.aai ,
from greet t2
join free t3 on t2.t1ID = t3.t1ID and t3.nl = :calda
left join t1 ex on ex.t1ID= t2.t1ID
where 1 = 1
#if($params.cName) and t2.nom = :cName #end
#if($params.cSurname) and t2.tr = :cSurname #end
#if($params.cColour) and t2.bl = :cColour #end
#if($params.cExt) and t2.ta = :cExt #end
#if($params.cOrd) and t2.dn = :cOrd #end
#if($params.cBegin) and t2.fi = :cBegin #end
#if($params.cEnd) and t2.ff = :cEnd #end
#if($params.calda) and t3.nl = :calda #end
#if($params.area)
and trim(t2.aai)=:area
#end
#if( $params.isOld == 'N' )
AND (trunc(t2.ff) <![CDATA[>=]]> trunc(sysdate) or t2.ff is null)
#end
order by t2.dn, t2.t1ID desc
</entry>
I wanted to filter the results already obtained by this query, to get only those that do not appear in a new table t4 whose t4.scid corresponds to the table greet t2.scid