Hi,

> is this a buy or a feature?
>
> i try this in FB 1.5 rc3 and it works fine
>
> create view "requestsummary"
> ("RequestDate",
> "ItemID",
> "Qty",
> "Use",
> "Name",
> "Unit")
> as
> select "RequestDate", m."ItemID", sum(m."Qty") as "Qty",
> sum(m."Use") as "Use",
> (select "Name" from "items" d where d.ID = m."ItemID") as "Name",
> (select "Unit2" from "items" d2 where d2.ID = m."ItemID")
> as "Unit"
> from "usage" m
> where "Status" != 'CAN'
> group by "RequestDate", "ItemID"
>
> but the same script in IB 6 open source edition give me an error
> message. I know the error message is that the Name and the Unit field
> must be included in the group by clause to fix the error. But it
> works here in FB 1.5

Indeed, IB 6.x / 7.x / FB 1.0 doesn't handle sub-queries well in a aggregate
query.

> before that script I have this one ...
>
> create view "requestsummary"
> ("RequestDate",
> "ItemID",
> "Qty",
> "Use",
> "Name",
> "Unit")
> as
> select "RequestDate", m."ItemID", sum(m."Qty") as "Qty",
> sum(m."Use") as "Use", d."Name", d."Unit2"
> from "usage" m left join "items" d on d.ID = m."ItemID"
> where "Status" != 'CAN'
> group by "RequestDate", "ItemID"

> which neither the FB 1.5 rc3 and IB open source edition give the same
> error.

The error is oke, you need to add d."Name", d."Unit2" to the GROUP BY
clause.

> But I wonder which is right the FB 1.5 rc3 or IB 6 open source ed. I
> wonder what is the result in FB 1.0.....

FB1.5 has a much better aggregate abilities and error-checking.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81