Simple Calculated Fields in Group By on ReportBuilder 11.06

rbuser

ReportBuilder 11.06
Delphi 2007 / Delphi 2010

Hi,

I have a report with a pipeline with four calculated fields.
Two, with aggregate functions:
SUM(MOV_1.QTA) ,
SUM(MOV_1.PRZ*MOV_1.QTA)),
Two, with simple string concatenations:
SOGSOG_1.DES||' - '||SOGSOG_1.IDE ,
SEDSOG_1.DES||' - '||SEDSOG_1.IDE

Obviously, the query has a group by clause, but ReportBuilder puts the last
two expressions in the group by clause as they are expressed in the
calculated fields.

If I check the query pushing the Sql button I see the correct query text,
but if I check it within the MagigSQL or executing the report I see a query
this:

SELECT DISTINCT SOGSOG_1.IDE, SOGSOG_1.COD,
SOGSOG_1.DES, SOGSOG_1.IND,
SOGSOG_1.CAP, SOGSOG_1.LOC,
SOGSOG_1.PRO, SOGSOG_1.TEL,
SOGSOG_1.FAX, SOGSOG_1.WWW,
SOGSOG_1.GEM, SOGSOG_1.CTR,
SEDSOG_1.IDE IDE_2,
SEDSOG_1.DES DES_2,
SEDSOG_1.IND IND_2,
SEDSOG_1.CAP CAP_2,
SEDSOG_1.LOC LOC_2,
SEDSOG_1.PRO PRO_2,
SEDSOG_1.TEL TEL_2,
SEDSOG_1.FAX FAX_2, SEDSOG_1.SMA,
SEDSOG_1.RIF, SEDSOG_1.SOGSOG,
SEDSOG_1.ZONSOG, SEDSOG_1.MANSOG,
SEDSOG_1.MMA, SEDSOG_1.DAM,
SEDSOG_1.REG, SEDSOG_1.TAV,
SEDSOG_1.ATTIVO, GIO_1.TIPO,
CFAART_1.DES DES_3,
CFAART_1.IDE IDE_4,
CFAART_1.COD COD_2,
SOGSOG_1.DES || ' - ' || SOGSOG_1.IDE
SOGSOG_1_DES_SOGSOG_1_IDE,
SEDSOG_1.DES || ' - ' || SEDSOG_1.IDE
SEDSOG_1_DES_SEDSOG_1_IDE,
SUM(MOV_1.QTA) SUM_MOV_1_QTA,
SUM(MOV_1.PRZ*MOV_1.QTA) SUM_MOV_1_PRZ_MOV_1_QTA
FROM SOGSOG SOGSOG_1
INNER JOIN SEDSOG SEDSOG_1 ON
(SEDSOG_1.SOGSOG = SOGSOG_1.IDE)
LEFT OUTER JOIN GIO GIO_1 ON
(GIO_1.SEDSOG = SEDSOG_1.IDE)
INNER JOIN MOV MOV_1 ON
(MOV_1.GIO = GIO_1.IDE)
INNER JOIN ARTART ARTART_1 ON
(ARTART_1.IDE = MOV_1.ARTART)
INNER JOIN CFAART CFAART_1 ON
(CFAART_1.IDE = ARTART_1.CFAART)
WHERE ( GIO_1.FORSOG IS NULL )
AND ( GIO_1.DATAEFFETTIVA BETWEEN '01-01-1900' AND '12-31-2090' )
AND ( GIO_1.STATO = 'C' )
GROUP BY SOGSOG_1.IDE, SOGSOG_1.COD,
SOGSOG_1.DES, SOGSOG_1.IND,
SOGSOG_1.CAP, SOGSOG_1.LOC,
SOGSOG_1.PRO, SOGSOG_1.TEL,
SOGSOG_1.FAX, SOGSOG_1.WWW,
SOGSOG_1.GEM, SOGSOG_1.CTR,
SEDSOG_1.IDE, SEDSOG_1.DES,
SEDSOG_1.IND, SEDSOG_1.CAP,
SEDSOG_1.LOC, SEDSOG_1.PRO,
SEDSOG_1.TEL, SEDSOG_1.FAX,
SEDSOG_1.SMA, SEDSOG_1.RIF,
SEDSOG_1.SOGSOG, SEDSOG_1.ZONSOG,
SEDSOG_1.MANSOG, SEDSOG_1.MMA,
SEDSOG_1.DAM, SEDSOG_1.REG,
SEDSOG_1.TAV, SEDSOG_1.ATTIVO,
GIO_1.TIPO, CFAART_1.DES, CFAART_1.IDE,
CFAART_1.COD,
SOGSOG_1.DES || ' - ' || SOGSOG_1.IDE, /* THIS IS WRONG AND
CAUSES AN ERROR */
SEDSOG_1.DES || ' - ' || SEDSOG_1.IDE /* THIS IS WRONG AND
CAUSES AN ERROR */
ORDER BY SOGSOG_1.DES, SEDSOG_1.DES, GIO_1.TIPO

This report worked properly before ReportBuilder 11.06.
I think this is a bug and I don't know how to avoid it.

Thank you in advance.
Massimiliano Trezzi.

nardmoseley

What database engine are you using?

According to the SQL standard, all non-aggregrate expressions from the SQL
Select must be included in the Group By.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi,

I'm using Firebird 1.5.2 and this query worked well until 11.06.
I'm not such an expert of the SQL standard, but I think that _all_fields_
used in all non-aggregate expressions from SQL Select must be in the group
by.
I wrote a lot of queries with Interbase and Firebird and I always followed
this rule without any problem.
Is the DADE plug-in involved in this process?

Thank you,
Massimiliano Trezzi

nardmoseley

You just repeated what I said

And that is what ReportBuilder is doing. The Sum( ) calcs are aggregrate
expression that operate over a set of rows. Thus the Sum( ) expressions are
/not/ included in the Group By. All other fields and expressions and are
included. The concatenation calcs are 'non-aggregrate expressions'.

If you want to modify this behavior see daMagicSQL.pas, the method
TdaMagicSQL.GetGroupByFields. You can comment out the code that loops over
the Calcs.




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

No, I'm saying that RB should add the fields not the expressions.
Consider the expression "SOGSOG_1.DES || ' - ' || SOGSOG_1.IDE": I think
that RB should check if SOGSOG_1.DES is included in the Group By clause,
and add it if not included; the same for SOGSOG_1.IDE.
I think this is right thing according to the SQL standard.

This Query does not work on Firebird 1.5 and probably in it doesn't work in
Interbase too.
In previous versions this not happened so I have a regression problem maybe
in hundreds of reports.
If you look in the normal Sql you see the right query, if you look MagicSql
you see the wrong one; why this difference?
I'd prefer to not change the code, because if I do it I must do it everytime
I upgrade.

Thank you in advance.
Massimiliano Trezzi

rbuser

Hi,

I always compile with runtime packages and I cannot avoid it.
So even if I fixed it, I could not compile your packages, so I'd be stuck
again.

Hi,
Massimiliano Trezzi.

nardmoseley

In my prior post, I only meant to change the code on your end as a temp fix
to get it working until we had more time to research it.

There is now a patch available for RB 11.06. With the patch, when using the
Designer.DataSettings.DatabaseType of dtInterbase, the non-aggregrate Calc
Fields will not be added to the Magic SQL Group By. Registered RB 11.06
customers can email support@ and request the patch.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Good news!

This should work on firebird too, I guess.
I will send you an email ASAP!

Thank you,
Massimiliano Trezzi.