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

>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Gordon Niessen
> Sent: 8. juni 2011 17:22
> To: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> Subject: Re: [firebird-support] Aggregating a Union
>
> On 6/8/2011 10:08 AM, Gordon Niessen wrote:
> >
> > I am using FB1.5 and am trying to do a GROUP BY on the result of a
> > UNION, but some how the syntax is eluding me.
> >
> > SELECT IC.item_id, IC.item_desc, SUM(IC1.qty), SUM(IC1.cost),
> > SUM(IC1.ext_cost)
> > FROM (
> > SELECT IC1.item_id, IC1.item_desc, -IC1.qty, -IC1.cost, -IC1.ext_cost
> > FROM ItemCost(12345) IC1
> > UNION
> > SELECT IC2.item_id, IC2.item_desc, IC1.qty, IC1.cost, IC1.ext_cost
> > FROM ItemCost(12350) IC2
> > ) IC
> > GROUP BY IC.item_id, IC.item_desc
> >
> > Do I have to embed the union set in a Stored Procedure to later do the
> > GROUP BY? I can't use a CASE on the qty, cost, and ext_cost by Order_ID
> > as the actual ItemCost is actually a Stored Procedure call and it does
> > not return Order_ID.
> >
> > Or is this not possible in FB1.5? How about FB2.5?
> >
> >I have confirmed it works in 2.5, but I need a solution for FB1.5 for a
> >number of customers.
>
> I don't even think SELECT ... FROM (SELECT ...) is supported in Fb
> 1.5. So if you don't use a stored procedure, you might need to do
> things quite a bit more cumbersome (I assume ItemCost is a simple
> selectable stored procedure that doesn't update/insert/delete anything):
>
> 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)
>
> HTH,
> Set
>
>

Looking at this further I realize it will not work well for us. To call
the stored procedure so many times has a lot of overhead. It is fairly
complex and goes three levels deep in stored procedure calls. So it
would slow down the results considerably.

I think we just need to push for FB2.5, which works on the subquery.
Though I wish I knew why doing a group by on ItemCostDelta() crashes
Firebird.

--
Thanks,

Gordon



[Non-text portions of this message have been removed]