Hi,

I have just moved back to using DBISAM after nearly a year away from it. I
am now writing an app in C# (VS2005) using ContextSofts DBISAM provider -
which all works fine.

My current project needs to be able to switch database backends depending on
the client's requirements. For smaller clients I was using VistaDB
(www.vistadb.net) as an embedded solution, but due to lots of problems with
VistaDB, I have moved back to DBISAM, and I'll be using either PGSQL or MS
SQL Server for larger clients.

One of the problems that I am now coming across (something that I had
forgotten about) is that DBISAM is sometimes a bit 'non-standard' with its
SQL implementation, and Error #11949 "Invalid use of non-aggregated
column..." is a case in point.

Here's the SQL I'm trying to execute :-

============================
select pay.id, paynum, datetime, pay.amount,  pay.per_id as
receivedfrom_per_id,
rtrim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' ||
coalesce(per.title,'')) as _receivedfrom,
paymethod_id, paymethod.name as _payentmethod,  pay.clinic_id, clinic.name
as _clinicname,  coalesce(sum(alloc.amount), 0) as amtallocated,
pay.amount - sum(alloc.amount) as amtunallocated,  /* <- PROBLEM LINE */
pay.createdon, pay.createdby_staff_id, cr_staff.knownas as _createdby,
pay.updatedon, pay.updatedby_staff_id, up_staff.knownas as _lastupdatedby

from pay
inner join clinic on (pay.clinic_id = clinic.id)
inner join per on (pay.per_id = per.id)
inner join paymethod on (pay.paymethod_id = paymethod.id)
left join alloc on (alloc.pay_id = pay.id)
left join staff as cr_staff on (pay.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (pay.updatedby_staff_id = up_staff.id)
group by pay.id, paynum, datetime, pay.amount, pay.per_id, per.lastname,
per.firstname, per.title, paymethod_id, paymethod.name, pay.clinic_id,
clinic.name, pay.createdon, pay.createdby_staff_id, cr_staff.knownas,
pay.updatedon, pay.updatedby_staff_id, up_staff.knownas;
============================

DBISAM doesn't allow me to perform "pay.amount - sum(alloc.amount)" because
one the fields is being aggregated and the other isn't.

This query runs fine under PGSQL, VistaDB and SQL Server. Two questions if I
may :-

[1] Will this query work under ElevateDB?
[2] What is the best work-around to get this working in DBISAM? (please
please please don't say 'create a memory table'!)

Thanks in advance!

Hmmm... maybe answering one of my own questions - here's the line that fails
:-

>> pay.amount - sum(alloc.amount) as amtunallocated,

pay = a payment record
alloc = a payment allocation records
You can have MANY allocations against ONE payment

I want to find out how much is left UNALLOCATED on a payment and the problem
is that DBISAM doesn't let us mix aggregated with non-aggregated, so I tried
this :-

>> sum(pay.amount) - sum(alloc.amount) as amtunallocated

WRONG! If there are TWO allocation records against a payment (say for £20)
then the result is £40. Three allocattion records would result in £60 and so
on.

However, what about this :-

>> (sum(pay.amount) / count(pay.id)) - sum(alloc.amount) as amtunallocated

Bizarrely, this seems to work happily under DBISAM (and PGSQL and SQL
Server). Of course, having to fudge the SQL in this way to make it
compatible with DBISAM is far from ideal, but I can't see anything wrong
with the maths.

Any comments as to whether this is a valid workaround for the
"non-aggregated/aggregrated" problem in DBISAM? Also, if someone could just
let me know whether ElevateDB has the same problem, then would also be much
appreciated - thanks!


"Hedley Muscroft" <hmuscroft@hotmail.com> wrote in message
news:F35B64D4-20D0-457E-9643-2A824DD5906D@news.elevatesoft.com...
> Hi,
>
> I have just moved back to using DBISAM after nearly a year away from it. I
> am now writing an app in C# (VS2005) using ContextSofts DBISAM provider -
> which all works fine.
>
> My current project needs to be able to switch database backends depending
> on the client's requirements. For smaller clients I was using VistaDB
> (www.vistadb.net) as an embedded solution, but due to lots of problems
> with VistaDB, I have moved back to DBISAM, and I'll be using either PGSQL
> or MS SQL Server for larger clients.
>
> One of the problems that I am now coming across (something that I had
> forgotten about) is that DBISAM is sometimes a bit 'non-standard' with its
> SQL implementation, and Error #11949 "Invalid use of non-aggregated
> column..." is a case in point.
>
> Here's the SQL I'm trying to execute :-
>
> ============================
> select pay.id, paynum, datetime, pay.amount,  pay.per_id as
> receivedfrom_per_id,
> rtrim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' ||
> coalesce(per.title,'')) as _receivedfrom,
> paymethod_id, paymethod.name as _payentmethod,  pay.clinic_id, clinic.name
> as _clinicname,  coalesce(sum(alloc.amount), 0) as amtallocated,
> pay.amount - sum(alloc.amount) as amtunallocated,  /* <- PROBLEM LINE */
> pay.createdon, pay.createdby_staff_id, cr_staff.knownas as _createdby,
> pay.updatedon, pay.updatedby_staff_id, up_staff.knownas as _lastupdatedby
>
> from pay
> inner join clinic on (pay.clinic_id = clinic.id)
> inner join per on (pay.per_id = per.id)
> inner join paymethod on (pay.paymethod_id = paymethod.id)
> left join alloc on (alloc.pay_id = pay.id)
> left join staff as cr_staff on (pay.createdby_staff_id = cr_staff.id)
> left join staff as up_staff on (pay.updatedby_staff_id = up_staff.id)
> group by pay.id, paynum, datetime, pay.amount, pay.per_id, per.lastname,
> per.firstname, per.title, paymethod_id, paymethod.name, pay.clinic_id,
> clinic.name, pay.createdon, pay.createdby_staff_id, cr_staff.knownas,
> pay.updatedon, pay.updatedby_staff_id, up_staff.knownas;
> ============================
>
> DBISAM doesn't allow me to perform "pay.amount - sum(alloc.amount)"
> because one the fields is being aggregated and the other isn't.
>
> This query runs fine under PGSQL, VistaDB and SQL Server. Two questions if
> I may :-
>
> [1] Will this query work under ElevateDB?
> [2] What is the best work-around to get this working in DBISAM? (please
> please please don't say 'create a memory table'!)
>
> Thanks in advance!
>

Hedly,

<< Any comments as to whether this is a valid workaround for the
"non-aggregated/aggregrated" problem in DBISAM? >>

It will work - you're just decomposing the SUM() back down into it's
non-aggregate form for the payment amount in a way that DBISAM likes.

<< Also, if someone could just let me know whether ElevateDB has the same
problem, then would also be much
appreciated - thanks! >>

Nope, it works just fine.   You can run something like this with no problem:

CREATE TABLE "Payments"
(
"CustNo" INTEGER,
"OrderNo" INTEGER,
"Amount" DECIMAL(20,2)
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768;

CREATE TABLE "PaymentItems"
(
"OrderNo" INTEGER,
"ItemNo" INTEGER,
"Amount" DECIMAL(20,2)
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768;

SELECT Payments.OrderNo,
Payments.Amount-SUM(PaymentItems.Amount) AS Balance
FROM Payments INNER JOIN PaymentItems ON
Payments.Orderno=PaymentItems.OrderNo
GROUP BY Payments.OrderNo

--
Tim Young
Elevate Software
www.elevatesoft.com