This gives you an idea, but not always accurate (especially if you purge F986110 – which you should). Note that this also compares against PD811 in OL.
Another handy thing about this script is the use of instr to carve out just the report name from the fndfuf2 field.
Note that the F986114 is pretty handy for this stuff, but this statement will work on all releases.
ORACLE
select
count(1), substr(jcfndfuf2, 1, instr(jcfndfuf2,'_')-1)
from svm811.f986110
where exists (
select 1
from ol811.f9860 t1, ol811.f9861 t2
where t1.siobnm = t2.siobnm
and t2.simrgmod = 'C'
and t2.sipathcd = 'PD811'
and t1.siobnm = substr(jcfndfuf2, 1, instr(jcfndfuf2, '_')-1))
group by substr(jcfndfuf2, 1, instr(jcfndfuf2, '_')-1)
order by 1 desc
AS/400
select
count(1), substring(jcfndfuf2, 1, charindex('_', jcfndfuf2)-1)
from svm811.f986110
where exists (
select 1
from ol811.f9860 t1, ol811.f9861 t2
where t1.siobnm = t2.siobnm
and t2.simrgmod = 'C'
and t2.sipathcd = 'PD811'
and t1.siobnm = substring(jcfndfuf2, 1, charindex('_', jcfndfuf2)-1))
group by substring(jcfndfuf2, 1, charindex('_', jcfndfuf2)-1)
order by 1 desc
No comments:
Post a Comment