Article View: borland.public.interbase.sql
Article #515Using EXISTS in views crashed IB (6.0)??
From: Arjan de Haan
Date: Tue, 13 Nov 2007 21:06
Date: Tue, 13 Nov 2007 21:06
28 lines
2314 bytes
2314 bytes
Hi.
We have an existing installation of IB Server 6 which I was updating to incorporate some
new views. When testing I ran across the following problem.
I have a view, which results depend on the existing of matching data in another view, like
this:
SELECT Locaties.LocatieOID, Locaties.Verwijderd, Locaties.Naam, Locaties.Tellergebruik,
Locaties.Tellersaan, Klanten.KlantOID, Klanten.Naam, Adressen.Plaats
FROM ((Locaties INNER JOIN Klanten ON Locaties.KlantOID = Klanten.KlantOID)
INNER JOIN Adressen ON Locaties.AdresOID = Adressen.AdresOID)
WHERE
(EXISTS (SELECT TijdelijkOID FROM Tijdelijk
WHERE (Tijdelijk.LocatieOID = Locaties.LocatieOID)))
OR
(EXISTS (SELECT LOCATIEOID FROM VIEWLOCAUTMCOUNT
WHERE VIEWLOCAUTMCOUNT.LocatieOID = Locaties.LocatieOID))
This query works, as long as i remove the EXISTS(....) on the ViewLocAutmCount from the
query. If it is used, even trying to explain the query (retrieving the plan) crashes IB!!
To explain the query (or what I'm trying to do): I need locations, but only those that
have either a reference from another table (Tijdelijk), or where equipment is placed
(ViewLocAutmCount, which lists the number of appliances placed at a location).
Anybody can show me what I'm doing wrong? Can't IB 6 handle referencing views from within
views??
Tx.
...Arjan...
Technical Details
Message-ID:
<473a11f4@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!wn13feed!worldnet.att.net!64.192.187.27!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mailShow Full Headers
Complete Article Headers
X-Google-Thread: 114a0e,2745675ecdb62401,start X-Google-Attributes: gid114a0e,public,usenet X-Google-Language: ENGLISH,ASCII-7-bit From: "Arjan de Haan" <2x nospam nospam adwhaan AT hotpop.com> Newsgroups: borland.public.interbase.sql Subject: Using EXISTS in views crashed IB (6.0)?? Date: Tue, 13 Nov 2007 22:06:31 +0100 Lines: 30 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original NNTP-Posting-Host: 82.170.245.232 Message-ID: <473a11f4@newsgroups.borland.com> X-Trace: newsgroups.borland.com 1194988020 82.170.245.232 (13 Nov 2007 13:07:00 -0700) Path: arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!wn13feed!worldnet.att.net!64.192.187.27!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail