Hi all,

I have a problem to generate sums. What I need is a result table looking
like this:

Article ID / Sale sum / Order sum

Sales and orders are in different tables, so I can't use a simple SUM()
GROUP BY Article.ID - the LEFT OUTER JOIN would lead to multiple records per
sale/order. Example: I have one sale (say, 2 items) and two orders (say, 2
and 3 items). If I use a LEFT ORDER, I end up with two records: one with
Sale = 1 and Order = 2, one with Sale = 1 and Order = 3. Other JOINs can
lead to even more duplicate lines. Summing up Sale will give the wrong
result (here: 2).

So, I want to create a temporary table, with two rows SaleSum and OrderSum,
both with a value of 0. Then, I can run two different UPDATEs to set SaleSum
and OrderSum: UPDATE TmpTable SET SaleSum = SaleSum + ...

Now my problem: that UPDATE simple does not work as expected. Here are two
demo tables:

CREATE TABLE IF NOT EXISTS "a"
(
  "id" AUTOINC,
  "name" VARCHAR(10),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 3
);

INSERT INTO "a" VALUES (1, 'AA');
INSERT INTO "a" VALUES (2, 'AB');
INSERT INTO "a" VALUES (3, 'AC');

CREATE TABLE IF NOT EXISTS "B"
(
  "ID" AUTOINC,
  "A_ID" INTEGER,
  "V" INTEGER,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 6
);

INSERT INTO "B" VALUES (1, 1, 1);
INSERT INTO "B" VALUES (2, 1, 1);
INSERT INTO "B" VALUES (3, 1, 1);
INSERT INTO "B" VALUES (4, 2, 1);
INSERT INTO "B" VALUES (5, 2, 1);
INSERT INTO "B" VALUES (6, 3, 1);


Simple stuff. Here is the temp table:

SELECT
 A.*,
 0 AS SumB
INTO "MEMORY\Tmp"
FROM A;

So, let's sum up using UPDATE:

UPDATE "MEMORY\Tmp" SET
 SumB = SumB + B.V
FROM "MEMORY\Tmp"
INNER JOIN B ON B.A_ID = "MEMORY\Tmp".ID;

And let's see what we get:

SELECT * FROM "MEMORY\Tmp"

Duh, SumB is 1 for all three records! Now let's try a SELECT, identical with
the UPDATE:

SELECT "MEMORY\Tmp".*, B.V
FROM "MEMORY\Tmp"
INNER JOIN B ON B.A_ID = "MEMORY\Tmp".ID;

That's a result set with 6 records, 3 of them for A.ID=1 and 2 for A.ID=2.
If you ask me, that UPDATE should behave exactly like this SELECT add all
the records.

Any idea what's going on in the backyard? Problem is, I need that function,
I can't use some trick solving the problem for exactly this table structure
because the whole routine is much more complex, and I need it in SQL. Oh,
_and_ I need it until tomorrow, as always.

IOW: Tim, help...

Ralf

Ralf, suggest simpler process would be

1) Create two separate temp tables grouping at the level that you want to
join your results first

    Select ID, sum(x) as xxx into table1
     Select ID, sum(Y) as yyy into table2
   index the id ...

    If there is any possibility they can be mismatched by ID, then create a
master first using distinct values

   ie Select ID
       into Master
       from  Table 1
       union
       Select ID
       from table1

    .. index the ID ...

     Then select m.ID, t1.xxx, t2.yyy
     from master
     left join table1 t1 on..
     left join table2 t2 on ..

Donat.

Ralf,

<< IOW: Tim, help... >>

It's a bug.  DBISAM specifically avoids updating the driver table multiple
times with joins to avoid the performance penalty.  Unfortunately, it also
causes this side effect.

--
Tim Young
Elevate Software
www.elevatesoft.com

Donat,

> Ralf, suggest simpler process would be

Thanks for the suggestion. Unfortunately, the example I used is a _very_
simple version of the real thing. It is part of a very complex mechanism
that generates the SQL statement. Every larger change means quite some
rework. So, I am looking for a more simple approach

Ralf

Tim,

....
> It's a bug.  DBISAM specifically avoids updating the driver table
> multiple times with joins to avoid the performance penalty. Unfortunately,
> it also causes this side effect.

That's what I thought, optimization can bite you where you definitely don't
want it. And because this UPDATE is not the only place or project where I
use it that way: anything I can do about it?

Btw, thank you (again) for your openness. We have to handle with Oracle DBAs
and found the obvious problems and some undocumented error codes. NO WAY
they would even call that a problem or even glitch.

Ralf

Ralf


Its well beyond my level of SQL knowledge, but what about using the wonderful custom function capability. Code it in OP wrap a custom function around it and use it in SQL.

Roy Lambert

Roy Lambert wrote:
....
> Its well beyond my level of SQL knowledge, but what about using the
> wonderful custom function capability. Code it in OP wrap a custom
> function around it and use it in SQL.

Because the application runs locally and in C/S mode, depending on the
individual customer, and because I always use the server out of the box. And
if it's a bug, it is much better to solve it than to find a way to
circumvent that at all. You never now when it will bite back. And Tim fixed
the problem in no time, as expected

Ralf

Ralf,

<< That's what I thought, optimization can bite you where you definitely
don't want it. And because this UPDATE is not the only place or project
where I use it that way: anything I can do about it? >>

You already know this, but the answer is "download Build 5".

<< Btw, thank you (again) for your openness. We have to handle with Oracle
DBAs and found the obvious problems and some undocumented error codes. NO
WAY they would even call that a problem or even glitch. >>

It's an efficiency and time thing - it's quicker and less of a hassle to
simply acknowledge the bug and fix it than it is to stonewall.

--
Tim Young
Elevate Software
www.elevatesoft.com