Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> ORA-00979: not a GROUP BY expression??
Hi Folks,
I am working in Visual Basic 6 with Oracle 8i Database.I am using a very complex query.Plz bear with me.
I am posting the query below:
(
SELECT
R.StorerKey AS StorerKey,
RD.Lottable03 AS Customer,
RD.Lottable07 as CustomerGroup,
R.TruckNo AS TruckNo,
R.TruckType AS TruckType,
max(RD.Lottable05) AS ReceiptDate,
R.ReceiptKey AS ReceiptKey,
RD.SKU AS SKU,
SKU.DESCR AS Descr,
sum(RD.QtyExpected) as Expected, sum(RD.QtyReceived) as Recieved, sum(RD.QtyReceived * SKU.STDCube) as Volume, RD.Lottable02 as COrigin,
LA.Lottable07 = RD.Lottable07 and LA.Lottable01 = RD.Lottable01 and LA.SKU = RD.SKU and LA.Lottable02 = RD.Lottable02 and LA.Lottable04 = RD.Lottable04 and LA.Lottable06 = RD.Lottable06 and R.ReceiptKey in(Select LA.Lottable01 from LotAttribute LA where LA.Lot in (Select lot from adjustmentDetail AD, Adjustment A where AD.AdjustmentKey = A.AdjustmentKey AND ReasonCode = 'ASNDATAERR')) and ( (1 = 2)) and R.ReceiptKey in (select distinct receiptkey from receiptdetail where receiptkey not in (select distinct a.receiptkey from receiptdetail a, receiptdetail b where a.receiptkey = b.receiptkey ) union select distinct receiptkey from receiptdetail where receiptkey not in (select distinct a.receiptkey from receiptdetail a, receiptdetail b where a.receiptkey = b.receiptkey )) group by R.ReceiptKey,R.storerkey
A quick reference after "Union All" in the first select statement i get an error "ORA-00979: not a GROUP BY expression" while using "sum(RD2.QtyExpected)"
Questions:
1.What is wrong with using "sum" in that particular postion??Send me links to support this query.
2.How i can i optimize this query??Is there any utility or any feature in Oracle or say "TOAD"(which i use) so that it says for e.g.u should use "Where exists" instead of "IN" etc.
3.How can i add these groups
"comp.databases.oracle.tools,powersoft.public.powerbuilder.database,powersoft.public.powerbuilder.general,comp.databases.oracle.misc,comp.databases.oracle.server"
in Outlook Express??
Thanxs in Advance!!!
Regards,
Smithesh Kollara Received on Fri Jul 05 2002 - 01:11:56 CDT