On 6/9/2011 1:27 AM, Svein Erling Tysvær wrote:

> SELECT IC1.item_id, IC1.item_desc,
> coalesce((select SUM(IC2A.qty)
> FROM ItemCost(12350) IC2A
> WHERE IC1.item_id = IC2A.item_id
> AND IC1.item_desc = IC2A.item_desc), 0) -
> (select SUM(IC1A.qty)
> FROM ItemCost(12345) IC1A
> WHERE IC1.item_id = IC1A.item_id
> AND IC1.item_desc = IC1A.item_desc) as qty,
> coalesce((select SUM(IC2A.cost)
> FROM ItemCost(12350) IC2A
> WHERE IC1.item_id = IC2A.item_id
> AND IC1.item_desc = IC2A.item_desc), 0) -
> (select SUM(IC1A.cost)
> FROM ItemCost(12345) IC1A
> WHERE IC1.item_id = IC1A.item_id
> AND IC1.item_desc = IC1A.item_desc) as cost,
> coalesce((select SUM(IC2A.ext_cost)
> FROM ItemCost(12350) IC2A
> WHERE IC1.item_id = IC2A.item_id
> AND IC1.item_desc = IC2A.item_desc), 0) -
> (select SUM(IC1A.ext_cost)
> FROM ItemCost(12345) IC1A
> WHERE IC1.item_id = IC1A.item_id
> AND IC1.item_desc = IC1A.item_desc) as ext_cost
> FROM ItemCost(12345) IC1
> UNION
> SELECT IC2.item_id, IC2.item_desc,
> (select SUM(IC2A.qty)
> FROM ItemCost(12350) IC2A
> WHERE IC2.item_id = IC2A.item_id
> AND IC2.item_desc = IC2A.item_desc),
> (select SUM(IC2A.cost)
> FROM ItemCost(12350) IC2A
> WHERE IC2.item_id = IC2A.item_id
> AND IC2.item_desc = IC2A.item_desc),
> (select SUM(IC2A.ext_cost)
> FROM ItemCost(12350) IC2A
> WHERE IC2.item_id = IC2A.item_id
> AND IC2.item_desc = IC2A.item_desc)
> FROM ItemCost(12350) IC2
> WHERE NOT EXISTS(SELECT * FROM ItemCost(12345) ICN1
> WHERE IC2.item_id = ICN1.item_id
> AND IC2.item_desc = ICN1.item_desc)

I'll try this kind of select. I tried to create a simple stored
procedure that does a UNION on the two calls the the ItemCost procedure,
but any attempts to call it cause a crash of the Firebird server. It is
a simple FOR SELECT Item_id, ... from ItemCost(12345) UNION SELECT ...
from ItemCost(12350) INTO :ItemId, ... DO BEGIN SUSPEND END END.

And I can call it and get the raw data. But if I try to group and sum()
it crashes to server.

--
Thanks,

Gordon