[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle SQL - Not a GROUP BY Expression in Subquery

Posted on 2014-03-13
3
Medium Priority
?
2,368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39926025
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
ID: 39926181
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 2000 total points
ID: 39926694
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

649 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