Problem with Calc Expressions

rbuser

Following my previous message, on 25/05/2003, with subject "Limited number
of Calculated fields allowed??", I now have to add that:

I have come to realise that it has nothing to do with the number of
expressions involved! Instead, it gets mixed up, when my select statement
uses an aggregate function. for example

expression1 : trunc(a_date)
expression2: (select max(another_date) from another_table where something =
something_else)

works fine, as long as my select statement that uses "MAX", is the last
expression of all! I cannot continue, adding expressions (even without
aggregate functions in them) because the "Not a group by Expression" error
message will appear! Which is something that I can do when writing normal
SQL!!

I hope I've given you a clear picture of the problem!
Thanks in advance
Mirto

digitalmetaphors

When you add a calc field in the dataview, it tries to build the GROUP BY
clause. Do you have a BLOB field in the GROUP BY that is causing the SQL
error. Ususally this is what happens. You'll have to remove the BLOB from
the select clause and use a lookup query dataview (master detail when there
is a one to one relationship for the lookup) to get the lone BLOB field so
that it isn't built into the GROUP BY clause.

We'll try to reproduce it here on our Oracle 9 database. What is the
simplest configuration that shows the problem? A single master with a detail
that has calc fields in the master? How many tables are in each dataview and
which one is the first table? Which table (by order they were added) is the
calc field created from? Which tables (again, by order in each dataview they
were added) are the linking fields from?

Can you reproduce it with a DBDemos report using the similar tables such
that you have the same relationships in the dataviews as you oracle tables?
If you could reproduce it in DBDemos, then send us a small example that
shows the problem.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Mirto,

I believe I had the same issue. For me it was caused by RB being too
helpful TdaSQL.HasAggregates (in daSQL.pas) checks for aggregate
functions anywhere in your expression (eg. Pos('SUM(', lsExpression) >
0)

I changed that to only be at the start of the expression to allow for
subselects (eg. Pos('SUM(', lsExpression) = 1)

HTH.

Cheers,

rbuser

Thank you, Rick, I think that is exactly what is causing my problem!
But I cannot and don't want to access and change the source code!

Jim, I hope you find Rick's reply indicative of the problem!
I didn't manage to reproduce an example in DBDemos, as a "List Index out of
bounds" error comes out, the minute I try to enter an expression, even a
"trunc(a_date)" one. And then I realised that it is a Paradox db, and does
not support these statements.

But imagine, I want an SQL created from RBuilder, that looks like:

select
a.custno,
*** First Selected Field
b.orderno,
*** Second Selected Field
trunc(a.LastInvoiceDate),
*** First expression
(select count(*) from items where items.orderno = b.orderno), ***
Second Expression
trunc(anotherDateField)
*** Third Expression

from customers a, orders b
where a.custno = b.custno

(Selecting the two tables Customers and Orders, selecting two fields (one
from each), and then continue by writing the Calc expressions )
The above I believe is a valid SQL statement in any Oracle editor.

I hope this gives you an idea, but I think Rick has given you the exact
position of the problem programmatically!

Thanks again!
Mirto



? "Rick Matthews" ?????? ??? ??????