Модераторы: m0p3e, edward_K, Модераторы
Код: Выделить всё
select sysdate as "data_viborki",gal.to_oradate2(b.lastdate) as "data_modif",gal.to_oratime2(b.lasttime) as "vremia_modif",
a.fnrec as nrec,gal.to_oradate2(a.fdatob) as "data_obor",
tuneval.fstrval as "grup",
c.xu$loginname as "usrname",
(case when b.operation=8 then 'удаление' when b.operation=4 then 'модифификация' else 'создание' end) as operation,
(case when a.j$$newoldrec=1 then 'новое знач.' else 'старое знач.' end) as "status",
a.fscheto as "dt_sch",
a.fsubossch as "dt_sub",
katpodr_d.fname as "podr_dt",
a.fschetk as "kt_sch",a.fsubschk as "kt_sub",katpodr_k.fname as "kt_podr",a.fsumob as "sumob",d.fname as "plansch",
katdoc.fname as "doc", a.fnodok as "docnum",
spkau1.fname as kauos1,spkau1.tcode as tablos1,spkau1.acode as kaukod1,
spkau2.fname as kauos2,spkau2.tcode as tablos2,spkau2.acode as kaukod2,
spkau3.fname as kauos3,spkau3.tcode as tablos3,spkau3.acode as kaukod3,
spkau4.fname as kauos4,spkau4.tcode as tablos4,spkau4.acode as kaukod4,
spkau5.fname as kauos5,spkau5.tcode as tablos5,spkau5.acode as kaukod5,
spkau6.fname as kauos6,spkau6.tcode as tablos6,spkau6.acode as kaukod6,
spkau7.fname as kauks1, spkau7.tcode as tablks1, spkau7.acode as kaukodk1,
spkau8.fname as kauks2, spkau8.tcode as tablks2, spkau8.acode as kaukodk2,
spkau9.fname as kauks3, spkau9.tcode as tablks3, spkau9.acode as kaukodk3,
spkau10.fname as kauks4,spkau10.tcode as tablks4,spkau10.acode as kaukodk4,
spkau11.fname as kauks5,spkau11.tcode as tablks5,spkau11.acode as kaukodk5,
spkau12.fname as kauks6,spkau12.tcode as tablks6, spkau12.acode as kaukodk6
from gal.x$journal b left join gal.j$9011 a on b.nrec=a.j$$link
left join gal.x$users c on b.usercode=c.atl_nrec
left join gal.planssch d on a.fcplanssch=d.fnrec
left join gal.katpodr katpodr_d on katpodr_d.fnrec=a.fkodspo
left join gal.katpodr katpodr_k on katpodr_k.fnrec=a.fkodspk
left join gal.katdoc katdoc on katdoc.ftidkgal=a.ftidkgal
left join gal.tuneval tuneval on (tuneval.fcuser=c.atl_nrec and tuneval.fstrempty='USER.DESGR')
left join spkau_view spkau1 on (a."FTBLOS[1]"=spkau1.tcode and a."FKAUOS[1]"=spkau1.fnrec)
left join spkau_view spkau2 on (a."FTBLOS[2]"=spkau2.tcode and a."FKAUOS[2]"=spkau2.fnrec)
left join spkau_view spkau3 on (a."FTBLOS[3]"=spkau3.tcode and a."FKAUOS[3]"=spkau3.fnrec)
left join spkau_view spkau4 on (a."FTBLOS[4]"=spkau4.tcode and a."FKAUOS[4]"=spkau4.fnrec)
left join spkau_view spkau5 on (a."FTBLOS[5]"=spkau5.tcode and a."FKAUOS[5]"=spkau5.fnrec)
left join spkau_view spkau6 on (a."FTBLOS[6]"=spkau6.tcode and a."FKAUOS[6]"=spkau6.fnrec)
left join spkau_view spkau7 on (a."FTBLKS[1]"=spkau7.tcode and a."FKAUKS[1]"=spkau7.fnrec)
left join spkau_view spkau8 on (a."FTBLKS[2]"=spkau8.tcode and a."FKAUKS[2]"=spkau8.fnrec)
left join spkau_view spkau9 on (a."FTBLKS[3]"=spkau9.tcode and a."FKAUKS[3]"=spkau9.fnrec)
left join spkau_view spkau10 on (a."FTBLKS[4]"=spkau10.tcode and a."FKAUKS[4]"=spkau10.fnrec)
left join spkau_view spkau11 on (a."FTBLKS[5]"=spkau11.tcode and a."FKAUKS[5]"=spkau11.fnrec)
left join spkau_view spkau12 on (a."FTBLKS[6]"=spkau12.tcode and a."FKAUKS[6]"=spkau12.fnrec)
where b.tablecode = 9011
and a.fdatob < (select datzakr.fdateval from gal.tuneval datzakr where datzakr.fnrec='8138000000023499')
and gal.to_oradate2(a.fdatob) is not null
and b.lastdate < gal.TO_ATLDATE(TO_DATE(to_char(sysdate-1,'dd/mm/yyyy'),'DD/MM/YYYY'))