Take out new field crossing with another table large query

0

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

    
asked by JStack 21.12.2018 в 08:55
source

0 answers