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

On 4 Jul 2002 23:11:56 -0700, smithesh_kollara_at_hotmail.com (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.
>

If you are summing you need to GROUP BY all unaggregated fields.

>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.
>

Run an explain plan. Check the oracle documentation.

The first 20 lines of the query are legible. Try replacing the in and not in with exists and not exists.

Remove the cases where (1 = 2) it looks like you are using UNION ALL to join mutually exclusive queries. Build a query using only the queries that will return a result.

Try optimizing the separate parts of the union all separately.

/Bill

>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 - 18:59:48 CDT