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 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #430077 is a reply to message #430074] Sun, 08 November 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Someyhing in your select fields is neither an aggregate neither in the group by clause.

Use SQL*Plus and copy and paste your session.

Regards
Michel
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #430082 is a reply to message #430079] Sun, 08 November 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In b twoeight and twonine are neither aggregate expression nor in group by clause.

Regards
Michel

Re: Help In tuning but facing "Not Group By expression error" [message #430085 is a reply to message #430082] Sun, 08 November 2009 02:21 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

What cud be the probable solution for this..I tried alot .Please help me,..
Re: Help In tuning but facing "Not Group By expression error" [message #430089 is a reply to message #430085] Sun, 08 November 2009 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know the purpose of your query.
You bave to either use an aggregate function on these fields or put them in the group by clause.

Regards
Michel
Re: Help In tuning but facing "Not Group By expression error" [message #430090 is a reply to message #430089] Sun, 08 November 2009 02:55 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Purpose is nothing but Reduce the execution time and by that i can avoid more hits on tables.

Can u post the code where u feel to change ?..

Thanks again

Seyed.
Re: Help In tuning but facing "Not Group By expression error" [message #430097 is a reply to message #430090] Sun, 08 November 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know any "u".
And I don't know the purpose of your query not your post.

Quote:
You have to either use an aggregate function on these fields or put them in the group by clause.

No more to say.

Regards
Michel
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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:

select 1 from dual;


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 #430128 is a reply to message #430103] Mon, 09 November 2009 00:31 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Thanks Thomas,

Am still facing a big issue with this .can you help me in some way to make that get executed in short time since its taking too much time.

Re: Help In tuning but facing "Not Group By expression error" [message #430130 is a reply to message #430128] Mon, 09 November 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post what is required and requested?

Regards
Michel
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Help In tuning but facing "Not Group By expression error" [message #430146 is a reply to message #430144] Mon, 09 November 2009 02:49 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Based on the information you posted the only valid suggestion to make is faster would be "buy faster hardware".

Everything else would depend on things you refuse to tell us.
Previous Topic: Except Friday Query
Next Topic: Query regarding subqueries & execution plan
Goto Forum:
  


Current Time: Thu Dec 08 01:54:34 CST 2016

Total time taken to generate the page: 0.05948 seconds