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

List index out of bounds(2) when inserting new Group Search Criteria

edited September 2011 in DADE
I get a : List index out of bounds(2) when inserting new Group Search
Criteria.

I followed it in the debugger and landed on
"UpdateCalcSearchCriteriaDataTypes" in daSQL.pas.
The GetFieldsForSQL call doesn't return any values but there are Calc
criteria's, so in the end, lbFound is true and liIndex2 > number of elements
in lFields which is 0.

I was hoping this was the same problem as the one referred to by Ettore and
that the same fix can be applied.
But we are using RB 12.04 build 77.

I made a very simple report with only an employee table with job subtable, a
JobCount = "count(*)" calc variable and a group by search asking for
"JobCount > 2" ...

I noticed that when I'm finished filling the criteria for the group search,
the line stays in "edit" mode, like it's not yet been saved.
When I click ok on the error message and try to save it again it works...?


Chantal

Comments

  • edited September 2011
    Hi Chantal,

    For future reference, please use your name in the "from" field when
    posting to these newsgroups.

    In my quick testing with the latest version of ReportBuilder, I was
    unable to get a list out of bounds error by adding a group search field
    when there were no selected fields. Note that you actually have to be
    grouping on a field (whether it is selected or not) in order to use the
    HAVING clause.

    If possible, please send me the exact steps I can take here to recreate
    this issue using the DBDEMOS database.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited September 2011
    Sorry for the "from" name, windows mail filled it automatically with the
    group name, I think I've corrected this now.

    I think I found what our problem is.
    We don't use ado to reach the database but another component from RemObject,
    it appears that the standard function "GetFieldsForSQL" doesn't return the
    actual fields...which are apparently needed in RB 12 but this was never a
    problem before.

    We will update that part of the code and see if everything works...


  • edited September 2011
    It still doesn't work, but I get the fields back now so I can see why it's
    not working.

    I have 4 calculated fields, 2 of them with an expression (CASE WHEN...) ,
    one SOM and one COUNT.
    Only the 2 non aggregate expressions are added to the SQL that gets the
    fields. (GetFieldsForSQL).

    But my group search criteria uses the COUNT calc expression.

    So while looping to find the criteria.field in the calcfields list, it comes
    to index 3, which is not loaded in the lFields list because it's an
    aggregate function...

    The 2 lists don't have the same number of elements.
    lFields has the non-aggregate calculations (2 in my example)
    CalcFields has all calculation (4 in total)

    When I only define aggregate calculations the whole function
    (UpdateCalcSearchCriteriaDataTypes) is skipped because the resulting SQL has
    no
    "select" clause so it won't crash...

    It crashes the same way in the same function when having those 2 types of
    calculation and adding a search criteria on the non aggregate calculated
    field.

    I hope this can be fixed, I don't know yet the consequences of just clicking
    ok on the error message index out of bound and keep working...

    Chantal


  • edited September 2011
    Hi Chantal,

    What I really need is a reliable way of recreating this on my machine.
    As a test I tried recreating the scenario you described below but was
    unable to get an error when adding group search.

    If possible, please create a simple example using only Delphi and RB
    components I can run on my machine that demonstrates this issue and send
    it in .zip format to support@digital-metaphors.com and I'll take a look
    at it for you.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2011
    I'm trying to build a demo project in Delphi using the DBDEMOS database but
    I can't add any combination of "expression" and calcs.
    As soon as I've added a SUM or COUNT and try to add an expression
    (LOWER(city) for example) I get the message "Expressions in group by are not
    supported".

    What am I doing wrong?


  • edited October 2011
    Hi Chantal,

    It appears Paradox does not support the Having clause when there are
    non-aggregate expressions being selected.

    Which database are you using for your application? Is it possible you
    can send me an example I can run with that DB without needing any
    non-Delphi or non-RB components?

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2011
    We use MS SQL (2000, 2005, 2008, 2008R2).

    I'll create a simple SQL2000 database and use de ADO drivers.
    If I send you the .bak file you should be able to load it anywhere.


  • edited October 2011
    Patch received and it works fine now!
    Thank you

This discussion has been closed.