Re: Using EXISTS in views crashed IB (6.0)??

Article View: borland.public.interbase.sql

Article #521

Back to borland.public.interbase.sql Previous Next

#521

From: Arno Brinkman
Date: Wed, 14 Nov 2007 13:58

40 lines
2169 bytes

Hi,

You could try a workaround by bringing the view into the from clause.
May be the crash is related to view in the EXISTS.

SELECT
  DISTINCT
  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)
  LEFT JOIN VIEWLOCAUTMCOUNT vla ON (vla.LocatieOID = l.LocatieOID)
WHERE
  (EXISTS (SELECT t.TijdelijkOID FROM Tijdelijk t
    WHERE t.LocatieOID = l.LocatieOID))
  OR
  vla.LocatieOID IS NOT NULL

No idea what the performance is going to be here. It depends on how your view VIEWLOCAUTMCOUNT looks like.

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

Technical Details

Message-ID: <473aff1f$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <473a11f4@newsgroups.borland.com> <473abb2c@newsgroups.borland.com> <473af3ed$1@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,2745675ecdb62401
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Arno Brinkman" <bnewsgroups @ REMOVE_ME abvisie.nl>
Newsgroups: borland.public.interbase.sql
References: <473a11f4@newsgroups.borland.com> <473abb2c@newsgroups.borland.com> <473af3ed$1@newsgroups.borland.com>
Subject: Re: Using EXISTS in views crashed IB (6.0)??
Date: Wed, 14 Nov 2007 14:58:53 +0100
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 84.81.40.49
Message-ID: <473aff1f$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1195048735 84.81.40.49 (14 Nov 2007 05:58:55 -0700)
Path: arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message