Дубли книг

список книг дублей за 2008год

select
V.ID
,V.R_TYPE
,V.NO
,decode(v.DEPT_ID,v.DEPT_ID,(select dept.SHORT_NAME from rp_depts dept where dept.id=v.DEPT_ID)) "Отдел"
from
vol_volumes v  
where
v.no like '%2008%'
and (
    select 
    count(VV.ID)
    from
    vol_volumes vv  
    where
    vv.no = v.no
    and vv.r_type=v.r_type
    and vv.id<>V.ID
    )>0

order by v.no,v.r_type

Список КУВД

select 
ap.id "ID"
,AP.NO "КУВД" 
,decode(v.DEPT_ID,v.DEPT_ID,(select dept.SHORT_NAME from rp_depts dept where dept.id=v.DEPT_ID)) "Отдел"
from
app_applications ap
where
ap.vol_id in
(
    select
    V.ID
    from
    vol_volumes v  
    where
    v.no like '%2008%'
    and V.R_TYPE='APP'
    and (
            select 
            count(VV.ID)
            from
            vol_volumes vv  
            where
            vv.no = v.no
            and vv.r_type=v.r_type
            and vv.id<>V.ID
        )>0
)
and 
(
    select 
    count(ap2.id) 
    from
    app_applications ap2
    where
    ap2.no=ap.no
    and AP.ID<>AP2.ID
)>0