Substring im JOIN auf beiden Seiten

Code: Alles auswählen.

select
        t1.mandt,
        t2.psobtyp,
        t2.vtref,
        t1.opbel,
        t2.budat,
        t2.budgetyear,
        substr(t1.cpudt,1,4) as cpudtyear,
        t1.cpudt,
        t2.bldat,
        substr(t2.faedn, 1,4) as faednyear,
        substr(t2.faedn, 1,6) as faedJJJMM,
        t2.faedn,
        t2.hkont              as ford_hkont,
        t2.fmtyp,
        t2.fipex,
        t2.fistl,
        t2.hvorg,
        t2.tvorg,
        t3.hkont             as ertr_hkont,
        t3.gsber,
        t3.prctr,
        t3.posid,
        t2.betrh
  from
        DFKKKO t1
        inner join DFKKOP t2
        on 
            t1.mandt = t2.mandt
        and t1.opbel = t2.opbel
        and t1.cpudt < t2.faedn
        and substr(t1.cpudt,1,4) < substr(t2.faedn, 1,4)
        /* and t2.budat          <> t2.faedn */
        left join ZLGDPSCD_TVGKOFI t3
        on 
            t2.mandt   = t3.mandt
        and t2.psobtyp = t3.psobtyp
        and t2.hvorg   = t3.hvorg
        and t2.tvorg   = t3.tvorg
where
        t1.herkf             = 'TA'
   and  substr(t1.cpudt,1,4) = '2024'
   and  t2.psobtyp          >= '2195'
   and  t2.psobtyp          <= '2199'

Code: Alles auswählen.

and substr(t1.cpudt,1,4) < substr(t2.faedn, 1,4)

Code: Alles auswählen.

and SUBSTRING( t1~cpudt , 1 , 4 ) < SUBSTRING( t1~cpudt , 1 , 4 )