Using EXISTS in views crashed IB (6.0)??

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...

Arjan de Haan wrote:

> You say it might be an old bug. Is there a list available of known
> bugs in this (or later) versions?

QC is the best information available.

-- 

Note, however, that QC was not available until long after IB 6 so it is
likely that bugs in IB 6 that were fixed in later versions are not in
QC.

-- 

Użytkownik "Arjan de Haan" <2x nospam nospam adwhaan AT
hotpop.com> napisał 
w wiadomości news:473a11f4@newsgroups.borland.com...
> 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...
>

this is so old version of interbase - upgrade it
or use Firebird

Karol Bieniaszewski 

Hi Arjan,

> 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:
...

I'm not sure about the reason of the crash, but if it's due the PLAN try to use
aliasses and see if that helps 
(workaround of course).

SELECT
  l.LocatieOID, l.Verwijderd, l.Naam, l.Tellergebruik,
  l.Tellersaan, k.KlantOID, k.Naam, a.Plaats
FROM
  Locaties l
  JOIN Klanten k ON (l.KlantOID = k.KlantOID)
  JOIN Adressen a ON (l.AdresOID = a.AdresOID)
WHERE
  (EXISTS (SELECT t.TijdelijkOID FROM Tijdelijk t
    WHERE t.LocatieOID = l.LocatieOID))
  OR
  (EXISTS (SELECT vla.LOCATIEOID FROM VIEWLOCAUTMCOUNT vla
    WHERE vla.LocatieOID = l.LocatieOID))

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Firebird open source database with many powerful SQL features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

General database developer support:
  http://www.databasedevelopmentforum.com

Support list for Interbase and Firebird users:
  firebird-support@yahoogroups.com