Oracle DB spinnt

[p80286]

hallo zusammen,

set zwei Stunden versuche ich den Fehler zu finden komme aber nicht zu Potte

Code:

select v_srbib.Casereference,v_srbib.casekey, origin,henattorney,division,site,productpatent.*
from v_srbib
    ,v_references
    ,v_persons
    ,(select count(casekey) cnt,familykey from cases where familykey is not null group by familykey) src
    ,productpatent
where v_srbib.casekey=src.familykey(+)
  and src.cnt is null
  and v_srbib.Casetypekey=2
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241462)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241534)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241561)
--  and not exists ( select * from productpatent where productpatent.casefamilykey=v_srbib.casekey and productpatentkey is not null )
  and v_srbib.casekey=v_references.casekey(+)
  and v_srbib.casekey=v_persons.casekey(+)
  and v_srbib.casereference not like 'T%'
  and v_srbib.casekey=productpatent.casefamilykey(+)
--  and productpatentkey(+) is null
order by v_srbib.casereference

diese Abfrage liefert 130 Datensätze wobei man bei zwei Datensätzen einträge in Productpatent sieht.

Code:

  ..
  and v_srbib.casereference not like 'T%'
  and v_srbib.casekey=productpatent.casefamilykey(+)
  and productpatentkey(+) is null
order by v_srbib.casereference

diese Version liefert 130 Datensätze ohne das man Einträge in Productpatent sieht.

folgende Versionen liefern als ergebnis 0 Datensätze

Code:

  ...
  and v_srbib.casekey=v_persons.casekey(+)
  and v_srbib.casereference not like 'T%'
  and v_srbib.casekey=productpatent.casefamilykey(+)
  and productpatentkey is null
order by v_srbib.casereference

Code:

select v_srbib.Casereference,v_srbib.casekey, origin,henattorney,division,site,productpatent.*
from v_srbib
    ,v_references
    ,v_persons
    ,(select count(casekey) cnt,familykey from cases where familykey is not null group by familykey) src
    ,productpatent
where v_srbib.casekey=src.familykey(+)
  and src.cnt is null
  and v_srbib.Casetypekey=2
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241462)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241534)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241561)
  and not exists ( select * from productpatent where productpatent.casefamilykey=v_srbib.casekey and productpatentkey is not null )
  and v_srbib.casekey=v_references.casekey(+)
  and v_srbib.casekey=v_persons.casekey(+)
  and v_srbib.casereference not like 'T%'
  and v_srbib.casekey=productpatent.casefamilykey(+)
--  and productpatentkey(+) is null
order by v_srbib.casereference

select v_srbib.Casereference,v_srbib.casekey, origin,henattorney,division,site,productpatent.*
from v_srbib
    ,v_references
    ,v_persons
    ,(select count(casekey) cnt,familykey from cases where familykey is not null group by familykey) src
    ,productpatent
where v_srbib.casekey=src.familykey(+)
  and src.cnt is null
  and v_srbib.Casetypekey=2
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241462)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241534)
  and not exists ( select * from caseevent where caseevent.casekey=v_srbib.casekey and caseevent.Eventkey=20241561)
  and exists ( select * from productpatent where productpatent.casefamilykey=v_srbib.casekey and productpatentkey is not null )
  and v_srbib.casekey=v_references.casekey(+)
  and v_srbib.casekey=v_persons.casekey(+)
  and v_srbib.casereference not like 'T%'
  and v_srbib.casekey=productpatent.casefamilykey(+)
--  and productpatentkey(+) is null
order by v_srbib.casereference

könnt Ihr mir einen Tip geben was ich übersehen habe?
(wenn ich das productpatent-Zeugs weglasse ist ales vollkommen in Ordnung)


Gruß
K-H