Solved

Oracle SQL - Not a GROUP BY Expression in Subquery

Posted on 2014-03-13
3
1,677 Views
Last Modified: 2014-03-14
When I run the script below I bump into the following error message: ORA-00979: not a GROUP BY expression.
I'm trying to get a grant total for the current month (Month To Date Qty) per Item from the main query.


select
t.eplant_id,
t.class,
t.itemno,
t.rev,
t.descrip2,
sum (t.trans_quan) as "Total Qty",
t.unit,
bom.mfgcell,
                    (select
                    sum (t2.trans_quan)
                   
                    from
                    translog t2 left outer join arinvt inv2 on(t2.arinvt_id=inv2.id)
                    left outer join standard bom on (inv2.standard_id = bom.id)

                    where
                    t2.arinvt_id = t.arinvt_id
                    and to_char(t2.act_date,'YYYY/MM') = to_char(sysdate,'YYYY/MM')
                    and t2.trans_in_out = 'IN'
                    and t2.trans_type like '%DISP%'
                    and t2.trans_quan <> 0
                    and bom.mfgcell = 'SEWING-A'

                    group by
                    t2.itemno) "Month To Date Qty"


from
  translog t left outer join arinvt inv on(t.arinvt_id=inv.id)
  left outer join standard bom on (inv.standard_id = bom.id)

where
  t.act_date >= sysdate -0.5
  and t.trans_in_out = 'IN'
  and t.trans_type like '%DISP%'
  and t.trans_quan <> 0
  and bom.mfgcell = 'SEWING-A'

group by
t.eplant_id,
t.class,
t.itemno,
t.rev,
t.descrip2,
t.unit,
bom.mfgcell
0
Comment
Question by:brainsurf1
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
that correlated subquery needs an aggregate function - or - it also has to be part of the group by clause. Try this simple change (see line 10) first:
select 
t.eplant_id,
t.class, 
t.itemno, 
t.rev, 
t.descrip2,
sum (t.trans_quan) as "Total Qty",
t.unit,
bom.mfgcell,
                MAX (select 
                    sum (t2.trans_quan)
                   
                    from
                    translog t2 left outer join arinvt inv2 on(t2.arinvt_id=inv2.id)
                    left outer join standard bom on (inv2.standard_id = bom.id)

                    where
                    t2.arinvt_id = t.arinvt_id 
                    and to_char(t2.act_date,'YYYY/MM') = to_char(sysdate,'YYYY/MM')
                    and t2.trans_in_out = 'IN'
                    and t2.trans_type like '%DISP%'
                    and t2.trans_quan <> 0 
                    and bom.mfgcell = 'SEWING-A'

                    group by
                    t2.itemno) "Month To Date Qty"


from
  translog t left outer join arinvt inv on(t.arinvt_id=inv.id)
  left outer join standard bom on (inv.standard_id = bom.id)

where
  t.act_date >= sysdate -0.5
  and t.trans_in_out = 'IN'
  and t.trans_type like '%DISP%'
  and t.trans_quan <> 0 
  and bom.mfgcell = 'SEWING-A'

group by
t.eplant_id,
t.class, 
t.itemno, 
t.rev, 
t.descrip2,
t.unit,
bom.mfgcell

Open in new window

But I suspect that whole subquery can be done a different way.
0
 

Author Comment

by:brainsurf1
Comment Utility
I Played around with aggregate function's and could not make this to work.
We need to figure out a alternative way to display the 'Month to date Qty'
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
Comment Utility
You could try something like this:
SELECT T.Eplant_Id
     , T.Class
     , T.Itemno
     , T.Rev
     , T.Descrip2
     , SUM ( CASE WHEN T.Act_Date >= SYSDATE - 0.5 
                  THEN T.Trans_Quan ELSE 0 END) AS "Total Qty"
     , T.Unit
     , Bom.Mfgcell
     , SUM ( T.Trans_Quan) "Month To Date Qty"
    FROM Translog T
         LEFT OUTER JOIN Arinvt Inv ON (T.Arinvt_Id = Inv.Id)
         LEFT OUTER JOIN STANDARD Bom ON (Inv.Standard_Id = Bom.Id)
   WHERE T.Act_Date >= TRUNC ( SYSDATE, 'Month')
     AND T.Trans_In_Out = 'IN'
     AND T.Trans_Type LIKE '%DISP%'
     AND T.Trans_Quan <> 0
     AND Bom.Mfgcell = 'SEWING-A'
GROUP BY T.Eplant_Id, T.Class, T.Itemno, T.Rev, T.Descrip2, T.Unit, Bom.Mfgcell

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now