Re: ORA-00979: not a GROUP BY expression??

Smithesh Kollara wrote:

> 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,
> RD.Lottable04 as CargoType,
> RD.Lottable06 as RefNo
> FROM
> Receipt R ,
> ReceiptDetail RD,
> SKU
> WHERE
> R.ReceiptKey = RD.ReceiptKey And
> SKU.SKU = RD.SKU and
> RD.Storerkey = SKU.Storerkey and
> R.ReceiptKey not 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
> ,RD.Lottable03,RD.Lottable07,r.Truckno,RD.SKU,
> RD.Lottable02,RD.Lottable04,RD.Lottable06,SKU.Descr,
> RD.Lottable01,R.TruckType ) UNION ALL (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, (Select
> sum(RD2.QtyExpected) from ReceiptDetail RD2 where rd2.Receiptkey =
> R.Receiptkey and RD2.SKU = RD.SKU) as Expected, (Select
> sum(RD1.QtyReceived) from ReceiptDetail RD1 where rd1.Receiptkey =
> R.Receiptkey and RD1.sku =RD.sku ) + (select sum(AD1.QTY) from
> AdjustmentDetail AD1 where AD1.lot = AD.lot ) as Received, ((Select
> sum(RD1.QtyReceived) from ReceiptDetail RD1 where rd1.Receiptkey =
> R.Receiptkey and RD1.sku =RD.sku ) + (select sum(AD1.QTY) from
> AdjustmentDetail AD1 where AD1.lot = AD.lot ) )* max(SKU.STDCube) as
> Volume,RD.Lottable02 as COrigin,RD.Lottable04 as CargoType,
> RD.Lottable06 as RefNo FROM Receipt R ,ReceiptDetail RD,SKU ,
> Adjustment A, AdjustmentDetail AD, LotAttribute LA WHERE R.ReceiptKey
> = RD.ReceiptKey And SKU.SKU = RD.SKU and RD.Storerkey = SKU.Storerkey
> and R.Receiptkey = LA.Lottable01 and LA.Lot = AD.LOT and
> A.AdjustmentKey = AD.AdjustmentKey and AD.ReasonCode = 'ASNDATAERR'
> and LA.StorerKey = R.StorerKey and LA.Lottable03 = RD.Lottable03 and
> 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
> ,RD.Lottable03,RD.Lottable07,r.Truckno,RD.SKU,
> RD.Lottable02,RD.Lottable04,RD.Lottable06,SKU.Descr,
> RD.Lottable01,R.TruckType,AD.Lot) order by StorerKey, Customer
> ,CustomerGroup, Truckno,ReceiptKey,SKU,COrigin,CargoType, RefNo
>
> 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

Your query is so ugly that I can't really get into it for lack of time. But if I found myself writing a query like this I would immediately start questioning the nature of the data and why I was doing what I was doing. At the very minimum I would break it up into a bunch of views, unit test it there, and then put it all back together.

I am also intriguied by the following: "and ( (1 = 2))". What is the expectation you have around this statement?

But the reason you are having GROUP BY problems is that you have no aggregate functions in that portion of the statement.

Daniel Morgan Received on Fri Jul 05 2002 - 10:59:37 CDT