--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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

Sorry, i give incomplete example, here is what i have:
I Have 1 table that i want to split into 2 views that select the
table where State = 1 on one view and State = 3 on another. I want to
have the sum of QTY from this 2 views. The views is joined on FK_ITEM
field.

I've tried this query:
Create View AVW as
SELECT ATB.*, 'IN' FROM ATB WHERE ATB.STATE = 1

Create View BVW as
SELECT ATB.*, 'OUT' FROM ATB WHERE ATB.STATE = 3

SELECT
COALESCE(AVW.ID,BVW.ID) ID,
COALESCE(AVW.FK_ITEM,BVW.FK_ITEM) FK_ITEM,
COALESCE(AVW.QTY,0) + COALESCE(B.QTY,0) QTY
FROM AVW
FULL JOIN BVW ON BVW.FK_ITEM = AVW.FK_ITEM

and it cause my server to hung, or not responding for a long period
of time. So i've tried this query:

SELECT
ATB1.ID,
ATB1.FK_ITEM,
COALESCE(
(SELECT ATB.QTY FROM ATB
WHERE ATB.ID = ATB1.ID AND
ATB.STATE = 1)
,0) +
COALESCE(
(SELECT ATB.QTY FROM ATB
WHERE ATB.ID = ATB1.ID AND
ATB.STATE = 3)
,0)
FROM ATB ATB1

with the time of execution and fetch as mention on my previous
example. Thanks in advance.

GB4Ever