🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: borland.public.interbase.sql
Article #515

Using EXISTS in views crashed IB (6.0)??

#515
From: Arjan de Haan
Date: Tue, 13 Nov 2007 21:06
28 lines
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-mail

Show 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