Home Datapipelines
New Blog Posts: Merging Reports - Part 1 and Part 2

Simple Calculated Fields in Group By on ReportBuilder 11.06

edited November 2009 in Datapipelines
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.

Comments

  • edited November 2009

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2009
    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



  • edited November 2009

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2009
    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
  • edited November 2009
    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.
  • edited November 2009

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2009
    Good news!

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

    Thank you,
    Massimiliano Trezzi.



This discussion has been closed.