Home » SQL & PL/SQL » SQL & PL/SQL » Help In tuning but facing "Not Group By expression error" (oracle 10g)
| Help In tuning but facing "Not Group By expression error" [message #430074] |
Sun, 08 November 2009 00:04  |
|
|
Hello all,
The code which u seeing just down is what i have written first. while it taking so much time to execute .So i have really done the similar code just after the following code by using of with.But Its giving me the error "ORA-00979: not a GROUP BY expression"..
--total uht +tomato paste + class codes AA,AB,AC + no offshore
SELECT sdep.name ,prefc.description,sreg.name,
--case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
and sls.depot=sdep.depot
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('A')
and prefc.class_code in ('AA','AB','AC')
and sdep.depot not in (82,83,85)
GROUP BY sdep.name,
prefc.description,sreg.name
-- case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end
--ORDER BY 1
union
-- total uht
SELECT sdep.name,'total uht',sreg.name,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.depot=sls.depot
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('A')
and sdep.depot not in (82,83,85)
GROUP BY sdep.name,sreg.name
--order by sreg.name
--order by sdep.name
union
--tomato paste
SELECT sdep.name,'Tomato Paste',sreg.name,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.depot=sls.depot
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('P')
and sdep.depot not in (82,83,85)
-- and prefc.class_code in ('PA','PB')
GROUP BY sdep.name,sreg.name
--order by sreg.rgncode
WITH a AS
(SELECT sdep.NAME NAME, prefc.description pref1,sreg.name sr,
--case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end,
SUM
(CASE
WHEN sls.docdt BETWEEN TO_DATE ('01-JAN-2008',
'DD-MON-YYYY'
)
AND TO_DATE ('31-OCT-2008',
'DD-MON-YYYY'
)
THEN slsd.amt1
ELSE 0
END
) twoeight,
SUM
(CASE
WHEN sls.docdt BETWEEN TO_DATE ('01-JAN-2009',
'DD-MON-YYYY'
)
AND TO_DATE ('31-OCT-2009',
'DD-MON-YYYY'
)
THEN slsd.amt1
ELSE 0
END
) twonine,
prefc.class_code classcode, prefc.refcode refcode
FROM sls_head sls,
sls_detail slsd,
prodfle pf,
prodref pref,
prodref_classification prefc,
customer c,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno = slsd.docno
AND sls.depot = slsd.depot
AND sls.docdt = slsd.docdt
AND sls.tc = slsd.tc
AND sls.depot = sdep.depot
AND sls.docdt BETWEEN TO_DATE ('01-JAN-2008', 'DD-MON-YYYY')
AND TO_DATE ('31-OCT-2009', 'DD-MON-YYYY')
AND slsd.prodcd = pf.prodcd
AND pf.refcode = pref.refcode
AND pf.class_code = prefc.class_code
AND c.depot = sls.depot
AND c.cusno = sls.cusno
AND c.custype = ct.code
AND sdep.rgncode = sreg.rgncode
AND sdep.depot NOT IN (82, 83, 85)),
b AS
(SELECT a.NAME, a.pref1,a.sr, twoeight "2008_data", twonine "2009_data"
FROM a
WHERE a.classcode IN ('AA', 'AB', 'AC') AND a.refcode IN ('A')
GROUP BY a.NAME, a.pref1,a.sr),
c AS
(SELECT a.NAME, 'Total UHT',a.sr, twoeight "2008_data", twonine "2009_data"
FROM a
WHERE a.refcode IN ('A')
GROUP BY a.NAME,a.sr),
d AS
(SELECT a.NAME, 'Tomato Paste',a.sr, twoeight "2008_data",
twonine "2009_data"
FROM a
WHERE a.refcode IN ('P')
GROUP BY a.NAME,a.sr)
SELECT *
FROM b
UNION
SELECT *
FROM d
UNION
SELECT *
FROM c
Thanks
Seyed.
|
|
|
|
|
|
| Re: Help In tuning but facing "Not Group By expression error" [message #430079 is a reply to message #430077] |
Sun, 08 November 2009 01:04  |
|
|
Thanks for your response Micheal,.
Am pasting here the last segment of the code
40 AND sls.docdt = slsd.docdt
41 AND sls.tc = slsd.tc
42 AND sls.depot = sdep.depot
43 AND sls.docdt BETWEEN TO_DATE ('01-JAN-2008', 'DD-MON-YYYY')
44 AND TO_DATE ('31-OCT-2009', 'DD-MON-YYYY')
45 AND slsd.prodcd = pf.prodcd
46 AND pf.refcode = pref.refcode
47 AND pf.class_code = prefc.class_code
48 AND c.depot = sls.depot
49 AND c.cusno = sls.cusno
50 AND c.custype = ct.code
51 AND sdep.rgncode = sreg.rgncode
52 AND sdep.depot NOT IN (82, 83, 85)),
53 b AS
54 (SELECT a.NAME, a.pref1,a.sr, twoeight "2008_data", twonine "2009_data"
55 FROM a
56 WHERE a.classcode IN ('AA', 'AB', 'AC') AND a.refcode IN ('A')
57 GROUP BY a.NAME, a.pref1,a.sr),
58 c AS
59 (SELECT a.NAME, 'Total UHT',a.sr, twoeight "2008_data", twonine "2009_data"
60 FROM a
61 WHERE a.refcode IN ('A')
62 GROUP BY a.NAME,a.sr),
63 d AS
64 (SELECT a.NAME, 'Tomato Paste',a.sr, twoeight "2008_data",
65 twonine "2009_data"
66 FROM a
67 WHERE a.refcode IN ('P')
68 GROUP BY a.NAME,a.sr)
69 SELECT *
70 FROM b
71 UNION
72 SELECT *
73 FROM d
74 UNION
75 SELECT *
76* FROM c
77 /
SELECT *
*
ERROR at line 69:
ORA-00979: not a GROUP BY expression
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help In tuning but facing "Not Group By expression error" [message #430103 is a reply to message #430097] |
Sun, 08 November 2009 06:17 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:
Purpose is nothing but Reduce the execution time and by that i can avoid more hits on tables.
If the only purpose of the query is to have a small execution time, and don't hit any tables, then you should use:
But I suspect there is also the purpose of "get some data", so I would suggest you have a look at the sticky post in the performance tuning section to see how you can find out which part of your query is the bottleneck.
|
|
|
|
|
|
|
|
| Re: Help In tuning but facing "Not Group By expression error" [message #430142 is a reply to message #430074] |
Mon, 09 November 2009 02:27 |
|
|
Its taking too much time to execute the code.I was trying to fix by with clause but facing problem over there.
this query am writing for the report which am creating.
--total uht +tomato paste + class codes AA,AB,AC + no offshore
SELECT sdep.name ,prefc.description,sreg.name,
--case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
and sls.depot=sdep.depot
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('A')
and prefc.class_code in ('AA','AB','AC')
and sdep.depot not in (82,83,85)
GROUP BY sdep.name,
prefc.description,sreg.name
-- case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end
--ORDER BY 1
union
-- total uht
SELECT sdep.name,'total uht',sreg.name,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.depot=sls.depot
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('A')
and sdep.depot not in (82,83,85)
GROUP BY sdep.name,sreg.name
--order by sreg.name
--order by sdep.name
union
--tomato paste
SELECT sdep.name,'Tomato Paste',sreg.name,
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY')
AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2008_data",
SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
then slsd.amt1
else 0 end) "2009_data"
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno=slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.depot=sls.depot
and sdep.rgncode=sreg.RGNCODE
AND prefc.refcode IN ('P')
and sdep.depot not in (82,83,85)
-- and prefc.class_code in ('PA','PB')
GROUP BY sdep.name,sreg.name
--order by sreg.rgncode
Can you tell me where all i can tune and increase the efficiency ?.
and also please tell where am making mistake in with clause which i posted in the first thread ? .
I tried avoiding group by and trying to use aggregate but still no hopes ..
|
|
|
|
| Re: Help In tuning but facing "Not Group By expression error" [message #430144 is a reply to message #430142] |
Mon, 09 November 2009 02:35 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Can you tell me where all i can tune and increase the efficiency ?.
Can you post the required and requested information as explained in the stickies?
You can post the same question as many times you want until you provide the appropriate information you won't have any help.
Quote:and also please tell where am making mistake in with clause which i posted in the first thread ? .
And in what the many answers I gave don't answer this question?
Quote:I tried avoiding group by and trying to use aggregate but still no hopes ..
And as you don't post what you tried and still don't explain your query what could we post?
Regards
Michel
[Updated on: Mon, 09 November 2009 02:37] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 09 09:19:01 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
