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)