select distinct sl.name location, t1.id rlnum, t2.id docnum, stf.surname from
(select d.location, d.id, d.createdat, l1.eventtime crtime, l2.eventtime uptime from smdocuments d, smdoclog l1, smdoclog l2 where d.id=l1.id and d.id=l2.id and l1.oldstate=-1 and l2.newstate=3 and d.doctype='RL' and docstate=3 and trunc(d.createdat)>=to_date('01.03.09')) t1, smdoclog t2, smstorelocations sl, smstaff stf where t2.docdate<t1.createdat and t2.eventtime between t1.crtime and t1.uptime and t2.newstate in (0,3) and t2.doctype in ('PO', 'PE', 'CS', 'CR', 'WO', 'WI') and (select count(*) from smspec s1, smspec s2 where s1.docid=t1.id and s2.docid=t2.id and s1.article=s2.article)<>0 and t1.location=sl.id and t2.employee=stf.id select distinct sl.name location, t1.id rlnum, t2.id docnum, stf.surname
from (select d.location,
d.doctype,
d.id,
d.createdat,
l1.eventtime crtime,
l2.eventtime uptime
from smdocuments d, smdoclog l1, smdoclog l2
where d.doctype=l1.doctype
and d.id = l1.id
and d.doctype=l2.doctype
and d.id = l2.id
and l1.oldstate = -1
and l2.newstate = 3
and d.doctype = 'RL'
and docstate = 3
and trunc(d.createdat) >= to_date('01.03.09')) t1,
smdoclog t2,
smstorelocations sl,
smstaff stf
where t2.docdate < t1.createdat
and t2.eventtime between t1.crtime and t1.uptime
and t2.newstate in (0, 3) -- Почему только эти два статуса отбираются? где 1 и 2 ?
and t2.doctype in ('PO', 'PE', 'CS', 'CR', 'WO', 'WI')
and (select count(*)
from smspec s1, smspec s2
where s1.doctype=t1.doctype
and s1.docid = t1.id
and s1.doctype=t2.doctype
and s2.docid = t2.id
and s1.article = s2.article) <> 0
and t1.location = sl.id
and t2.employee = stf.id select distinct sl.name location, t1.id rlnum, t2.doctype, t2.id docnum, stf.surname
from (select d.location,
d.doctype,
d.id,
d.createdat,
l1.eventtime crtime,
l2.eventtime uptime
from smdocuments d, smdoclog l1, smdoclog l2
where d.doctype=l1.doctype
and d.id = l1.id
and d.doctype=l2.doctype
and d.id = l2.id
and l1.oldstate = -1
and l2.newstate = 3
and d.doctype = 'RL'
and docstate = 3
and trunc(d.createdat) >= to_date(&MyDate, 'DD.MM.YYYY')) t1,
smdoclog t2,
smstorelocations sl,
smstaff stf
where t2.docdate < t1.createdat
and t2.eventtime between t1.crtime and t1.uptime
and t2.newstate in (0, 1, 2, 3)
and t2.doctype in ('PO', 'PE', 'CS', 'CR', 'WO', 'WI')
and (select count(*)
from smspec s1, smspec s2
where s1.doctype=t1.doctype
and s1.docid = t1.id
and s1.doctype=t2.doctype
and s2.docid = t2.id
and s1.article = s2.article) <> 0
and t1.location = sl.id
and t2.employee = stf.id