I've uploaded my sample database and query, please examine the query,
n many thanks to help me find out the workaround.

Thanks In Advance

GB4EVER

--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...>
wrote:

> Hi,
>
> At June 21, 2004, 00:00, gig_boy2001 wrote:
>
> > 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.
>
> If the query would simply return the field values(no coalesce), what
> would be the performance?
>
> > 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
>
> Try using aliases everywhere.
>
> In example:
>
> SELECT
> ATB1.ID,
> ATB1.FK_ITEM,
> COALESCE(
> (SELECT ATB2.QTY FROM ATB ATB2
> WHERE ATB2.ID = ATB1.ID AND
> ATB2.STATE = 1)
> ,0) +
> COALESCE(
> (SELECT ATB3.QTY FROM ATB ATB3
> WHERE ATB3.ID = ATB1.ID AND
> ATB3.STATE = 3)
> ,0)
> FROM ATB ATB1
>
> If I'm not mistaken there are issues when not using aliases
> everywhere, when using the same table/view in multiple areas of the
> query. I don't know if it's related to this scenario or not.
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)