Hi Sarge
Thanks for the reply, but the issue is not the column list, but
wrong values being populated in the insert .. select statement for the
column with aggregate functions, count(*) and sum(*).. I found
something in sqlserver forum along lines of these violating 1st nf in
db, and that we should do using
insert into <> ( select * from table ( select .... count(*),sum(*)) as
x )
Hope you got what I am referring. Nothing pertaining to syntax..

Need help.
Arun

Arun Srinivasan wrote:
> Hi Sarge
> Thanks for the reply, but the issue is not the column list, but
> wrong values being populated in the insert .. select statement for the
> column with aggregate functions, count(*) and sum(*).. I found
> something in sqlserver forum along lines of these violating 1st nf in
> db, and that we should do using
> insert into <> ( select * from table ( select .... count(*),sum(*)) as
> x )
> Hope you got what I am referring. Nothing pertaining to syntax..
As I said there is an APAR. Assuming that your example is correct(!)
Then COUNT() goes into c1, SUM() goes into count and whatever you
grouped by likely ended up in sum (!)
I can see how these are wrong results ....

If your example was not accurate please post an accurate example so we
can help you.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Example :

1 . insert into t1 (c1,c2,c3) (select b1, count(b2), sum(b3) from t2
group by b4,b5);
- this gives wrong values
2. insert into t2 (d1,d2,d3) (select * from table (select b1,
count(b2), sum(b3) from t2 group by b4,b5) as x ) this gives correct
values for the aggregate function colums.

Basically the select is the same statement, but written in a different
way, in (2) we are building the temp table and just throwing
everything in it to the t2, while in (1) we are inserting as we
calculate the count and sum functions.

This does not happen all the time, just a few times, thus we are
finding it a non-deterministic calculation that the db2 engine does..

Arun Srinivasan wrote:
> Example :
>
> 1 . insert into t1 (c1,c2,c3) (select b1, count(b2), sum(b3) from t2
> group by b4,b5);
> - this gives wrong values
> 2. insert into t2 (d1,d2,d3) (select * from table (select b1,
> count(b2), sum(b3) from t2 group by b4,b5) as x ) this gives correct
> values for the aggregate function colums.
>
> Basically the select is the same statement, but written in a different
> way, in (2) we are building the temp table and just throwing
> everything in it to the t2, while in (1) we are inserting as we
> calculate the count and sum functions.
>
> This does not happen all the time, just a few times, thus we are
> finding it a non-deterministic calculation that the db2 engine does..

ALL the examples you provided until now are syntactically wrong.
Please, please, provide your EXACT real queries.
It would also be nice to see your table definitions.

--
Jeroen

The Boss wrote:
>> This does not happen all the time, just a few times, thus we are
>> finding it a non-deterministic calculation that the db2 engine does..
>
> ALL the examples you provided until now are syntactically wrong.
> Please, please, provide your EXACT real queries.
> It would also be nice to see your table definitions.
Indeed. Maybe also time to tell the exact version platform of DB2.
Here is what happens on DB2 9.5 (and I'm confident on all DB2 for lUW
versions):
db2 => create table t(c1 int, c2 int, c3 int);
DB20000I The SQL command completed successfully.
db2 => create table s(c1 int, c2 int, c3 int, c4 int, c5 int);
DB20000I The SQL command completed successfully.
db2 => insert into t select c1, count(*), sum(c2) from s group by c3,
c4, c5;
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0119N An expression starting with "C1" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or
it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column
function and no GROUP BY clause is specified. SQLSTATE=42803

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab