Group By or Sum - run time error 3146

[Guest]

I get a run time error 3146 when I try to use the following SQl string

SELECT T1.T$ITEM, T1.T$CPRJ, T1.T$INVD, T1.T$DQUA, SUM (T1.T$AMNT), SUM
(T1.T$DQUA), TO_CHAR (T1.T$INVD, 'YYYYMM') PERIOD FROM "BAAN"."TTDSLS045115"
T1 WHERE TO_CHAR (T1.T$INVD, 'YYYYMM') = '200502' GROUP BY T1.T$ITEM

It works if I remove the SUM () and change Group By to Order By.
I am using MS DAO 3.6 and running Oracle 7 (BAAN) , Ofiice & Win 2000,
connectting via ODBC

Thanks

 

[Guest]

hi,
3146 - ODCB call failed.
The aggragate functions (sum) and group by don't work too
well togeather when you have a lot of thing to group by.
it seems to make it appear that the sum function isn't
working. i don't know but this may be what is crashing the
ODCB connection perticularly if it work when you change it.
if you want the sum, i suggest cutting the query back to
just item and amount.
not what you wanted to here but you have to be careful
using aggragate functions with group by.

 

[[MVP] S.Clark]

The table name or names are in quotes, and should not be. I think that the
period between them should be a comma, and this caused Access added the
quotes. Correct and try to run the query again.

 

[John Spencer (MVP)]

If this is supposed to be an Access Native query, I see several problems.
First, you have to group by all the fields that you don't use an aggregate
operator against. On the other hand if it is pass through query, I can't tell
you anything for sure since I am not familar with ORACLE's flavor of SQL. I do
think that you probably have to do the same thing in an Oracle aggregate query.

In Acccess, I'm not familar with the quoted syntax you are using in the FROM
clause, but since you say it runs if you get rid of the aggregate operators,
I'll accept that you may be able to solve your problem by setting group by for
the fields.

SELECT T1.T$ITEM, T1.T$CPRJ, T1.T$INVD, T1.T$DQUA, SUM (T1.T$AMNT),
SUM(T1.T$DQUA), TO_CHAR (T1.T$INVD, 'YYYYMM') PERIOD
FROM "BAAN"."TTDSLS045115" T1
WHERE TO_CHAR (T1.T$INVD, 'YYYYMM') = '200502'
GROUP BY T1.T$ITEM, T1.T$CPRJ, T1.T$INVD, T1.T$DQUA, TO_CHAR (T1.T$INVD, 'YYYYMM')