wrong "group by" clause

rbuser

RBuilder ver.10.06 and 10.05
To create a new one report including all the fields of one table.
Adding to a calculated field of type "expression" that contains an
aggregation operator (as an example "select sum(aImport) from aTable") and
subsequently an other calculated field of type "expression", it comes
generated erroneously a "group by" clause that it comprises all the fields
selects to you

nicocizik

Hi Vincenzo,

This is working as designed. It is necessary to add all non-aggregate
fields to the GROUP BY clause if you are selecting aggregate and
non-aggregate fields in the same dataset.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,
the aggregate operator is used in a subselect (calculated field as
"expression")
for this clause does not serve one "group by" on master query!. Carried out
the described steps previously in order to verify the wrong construction of
the query

nicocizik

Hi Vincenzo,

I apologize, but I'm still unclear about what is happening. If possible,
please post the incorrect SQL code that is being generated by the Query
Designer and perhaps what the SQL code should look like as well.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,
create a new one report with the demo ...\RBuilder\Demos\4. EndUser
Databases\Paradox\1. BDE\EndUser.dbsproj.
Select the table "Orders" including all the fields of table. Add a
calculated field of type "expression" (Select sum(itemstotal) from orders).
The SQL Statement generated correctly is:

SELECT orders.AmountPaid, orders.CustNo,
orders.EmpNo, orders.Freight,
orders.ItemsTotal, orders.OrderNo,
orders.PO, orders.PaymentMethod,
orders.SaleDate, orders.ShipDate,
orders.ShipToAddr1, orders.ShipToAddr2,
orders.ShipToCity, orders.ShipToContact,
orders.ShipToCountry, orders.ShipToPhone,
orders.ShipToState, orders.ShipToZip,
orders.ShipVIA, orders.TaxRate,
orders.Terms,
(Select sum(itemstotal) from orders) Select_sum_itemstotal_fr
FROM "orders.db" orders

Now add a new calculated field of type "expression" (Select sum(taxrate)
from orders).
The SQL Statement generated is wrong:

SELECT orders.AmountPaid, orders.CustNo,
orders.EmpNo, orders.Freight,
orders.ItemsTotal, orders.OrderNo,
orders.PO, orders.PaymentMethod,
orders.SaleDate, orders.ShipDate,
orders.ShipToAddr1, orders.ShipToAddr2,
orders.ShipToCity, orders.ShipToContact,
orders.ShipToCountry, orders.ShipToPhone,
orders.ShipToState, orders.ShipToZip,
orders.ShipVIA, orders.TaxRate,
orders.Terms,
(Select sum(itemstotal) from orders) Select_sum_itemstotal_fr,
(Select sum(taxrate) from orders) Select_sum_taxrate_from
FROM "orders.db" orders
GROUP BY orders.AmountPaid, orders.CustNo,
orders.EmpNo, orders.Freight,
orders.ItemsTotal, orders.OrderNo,
orders.PO, orders.PaymentMethod,
orders.SaleDate, orders.ShipDate,
orders.ShipToAddr1, orders.ShipToAddr2,
orders.ShipToCity, orders.ShipToContact,
orders.ShipToCountry,
orders.ShipToPhone, orders.ShipToState,
orders.ShipToZip, orders.ShipVIA,
orders.TaxRate, orders.Terms

The correct SQL Statement is:

SELECT orders.AmountPaid, orders.CustNo,
orders.EmpNo, orders.Freight,
orders.ItemsTotal, orders.OrderNo,
orders.PO, orders.PaymentMethod,
orders.SaleDate, orders.ShipDate,
orders.ShipToAddr1, orders.ShipToAddr2,
orders.ShipToCity, orders.ShipToContact,
orders.ShipToCountry, orders.ShipToPhone,
orders.ShipToState, orders.ShipToZip,
orders.ShipVIA, orders.TaxRate,
orders.Terms,
(Select sum(itemstotal) from orders) Select_sum_itemstotal_fr,
(Select sum(taxrate) from orders) Select_sum_taxrate_from
FROM "orders.db" orders

Regards

Vincenzo

nicocizik

HI Vincenzo,

Thanks for the example. There is now a patch that fixes this issue for RB
10.06. Send a small email to support@digital-metaphors.com if you would
like the patch.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,
thanks for the speed I will send one email

Vincenzo Versi