ORA-00979: not a GROUP BY expression??

From: Smithesh Kollara <smithesh_kollara_at_hotmail.com>
Date: 4 Jul 2002 23:11:56 -0700
Message-ID: <4b9ec61b.0207042211.6419c33a_at_posting.google.com>

[Quoted] Hi Folks,

[Quoted] 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, [Quoted] 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:

[Quoted] 1.What is wrong with using "sum" in that particular postion??Send me links to support this query.

[Quoted] 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
[Quoted] "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 - 08:11:56 CEST