At 09:18 PM 27/07/2006, you wrote:

>Hi all,
>
>I'm struggling with calculated fields.
>Let's say I've got an ib_query called ibq. It's got SQL like
>SELECT MYDATE, COUNT(MYBOOK), COUNT (MYCALLS)
>FROM FOO
>
>Now, I want to calculate COUNT(MYBOOK) / COUNT(MYCALLS) for every record.
>
>So I add a field like
>MYCALC FLOAT
>...to the calculated fields part of the ib_query.
>
>I tried writing the onCalculateField method as if I was writing for an
>ibquery but got all sorts of issues with that - along the lines of
>
>ibq.FieldByName('MYCALC').AsFloat = ibq.FieldByName('MYBOOK').AsFloat
>/ ibq.FieldByName('MYCALLS').AsFloat
>
>Now I'm experimenting with
> with AField do
> if FieldName = 'MYCALC' then
> AsFloat := ARow.ByName('MYBOOK').AsFloat /
>ARow.ByName('MYBOOK').AsFloat;
>end;
>
>...which failed, as does
>with AField do
> if FieldName = 'P_MYCALC' then
> AsFloat := ARow.GetColumnValue('MYBOOK').AsFloat /
> ARow.GetColumnValue('MYBOOK').AsFloat;
>
>This seems such a basic thing to do, I can't believe I can't do it...
>quite ignoring the fact that it'd give me divide by zero issues... ideas?

Get hold of a primer on SQL? Your problem is not with "how to make
IBO do what I want" but understanding how to extract data from your
database. You're writing an application with a relational database
as its back-end. The language that your application talks to the
database in is SQL. Whether you can believe it or not, you're not
going to get by without knowing SQL, sorry.

COUNT(MYBOOK) does *not* return a column named 'MYBOOK'. Your query
returns an output set consisting of a date and two unnamed integers
(the db engine will probably name them COUNT1 and COUNT2 if it
doesn't return them with blank names...depends on version...you can
check exactly what by running your query in a tool such as the DSQL
tab of IB_SQL). You are meant to supply names for derived
fields. All output records will contain exactly the same data except
for the date, and the query will take a very long time.

So - you then proceed to try to create a calculated field by dividing
one integer by another integer. But the code you provide refers to
no column that is in the output set. Even when you do stumble on the
names of the count fields, you're still doing an exercise in
futility, since the calculation is going to provide the same result
on all records.

What you're *really* after is an SQL SELECT statement involving
aggregation, so that you can derive counts per ??something?? Maybe,
per MYDATE, one can't guess. The syntax for getting aggregate values
uses GROUP BY. Once you have these aggregate values, which you would
be well-advised to assign names to, you will then have something on
which to compute your calculated field value row-by-row.

And, yes, you will need to take care of potential divide-by-zero when
you get around to defining your CalculateField method.

Helen