At 03:23 AM 21/06/2004 +0700, you wrote:

>Hi, i have problem with some queries,
>I have 2000 record on a table called ATB, and I have a view called AVW,
>this will select ATB with some parameter, so the view will be like this:
>
>CREATE VIEW AVW (..)
>AS
>SELECT ATB.*, 'IN' FROM ATB WHERE ATB.STATE = 1
>UNION ALL
>SELECT ATB.*, 'OUT' FROM ATB WHERE ATB.STATE = 3
>
>There is nothing wrong with the queries, and i can excecute the
>statement below 0.1 seconds, but with longer fetch time because I'm
>using MITEC IBQuery.
>The problem is when I want to do this query:
>
>SELECT
> AVW.
> COALESCE(
> (SELECT AVW.QTY FROM AVW WHERE AVW.ID = AVW1.ID)
> ,0)
>FROM AVW AVW1
>
>This simple query is executed in > 2 seconds and I never know the exact
>fetch time on IBQuery because it just to long to wait and when I use
>IBOConsole, it also not responding. I tried not to use Coalesce but it
>is the same, but when I remove the Correlated column, it can executed in
>less than 0.1 seconds, because it just the same as selecting the view
>only. What is the problem? And can you tell me more about Correlated
>Column? Thank for the help

The title of your posting "Coalesce with Full join very slow" has nothing
to do with the problem description. You don't have any joins here, full or
otherwise.

First, correlated subqueries are often slow - though, on 2000 rows, not
that slow.

Second, in re-entrant queries (and joins, too) you need to alias each "hit"
on the underlying table or view; and I also recommend that you assign a
fieldname to the runtime field. Hence:

SELECT
AVW1.*,
COALESCE(
(SELECT AVW2.QTY FROM AVW AVW2 WHERE AVW2.ID = AVW1.ID)
,0)) AS X_QTY
FROM AVW AVW1

Third, I can't see any point to the subquery here. What's wrong with

SELECT *,
COALESCE(QTY, 0) AS X_QTY
FROM AVW

/heLen