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

rbuser

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

nicocizik

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.

rbuser

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...

rbuser

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

nicocizik

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.

rbuser

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?

nicocizik

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?

rbuser

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.

rbuser

Patch received and it works fine now!
Thank you