Group by issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Group by issue

Author

Topic

chxxangie
Starting Member

16 Posts

Posted - 2010-07-29 : 05:49:33

Hi,
i am facing group by issue when running the query.
the error code is ORA-00979: not a GROUP BY expression.
i suspect is due to the subquery in the SELECT clause. but i have no idea to fix this issue.
appreciate your help in this....
thanks in advance.

SELECT
WHINR100.COMPANY,
WHINH210.SEQN,
FXINH039.OTBP,
WHINR100.BPID,
TCCOM100.NAMA,
WHINR100.ITEM,
WHINH210.RCNO,
FXINH051.btch,
case when (whinh210.ORNO in (select fxinh033.pdno from fxinh033))
then (select distinct a.PSNO from whinh210 a,fxinh033
where a.orno = fxinh033.orno
and a.pono = fxinh033.pono
and a.seqn = fxinh033.sqnb
and a.rcno = fxinh033.purr
and whinh210.orno = fxinh033.pdno
and whinh210.pono = fxinh033.ppon
and whinh210.seqn = fxinh033.psqn
and whinh210.rcno = fxinh033.sfcr)
else WHINH210.PSNO end,
WHINR100.TRDT_LOC,
SUM(WHINR100.QSTK) QSTK,
WHINR100.CWAR,
WHINR100.LOCA,
WHINR100.LOGN,
WHINR100.CLOT,
WHINR100.IDAT,
WHWMD300.LOCT,
WHINR100.KOOR,
WHINR100.ORNO,
WHINR100.PONO,
WHWMD400.OBPR,
DECODE(WHWMD300.LOCT,'1','Inspection','3','Receiving','4','Pick','5','Bulk','6','Staging'),
WHWMD210.ZONE,
TCIBD001.KITM
FROM
WHINH210,
WHINR100,
..............
..............
..............
GROUP BY
WHINR100.COMPANY,
WHINH210.SEQN,
FXINH039.OTBP,
WHINR100.BPID,
TCCOM100.NAMA,
WHINR100.ITEM,
WHINH210.RCNO,
FXINH051.btch,
WHINH210.PSNO,
WHINR100.TRDT_LOC,
WHINR100.CWAR,
WHINR100.LOCA,
WHINR100.LOGN,
WHINR100.CLOT,
WHINR100.IDAT,
WHWMD300.LOCT,
WHINR100.KOOR,
WHINR100.ORNO,
WHINR100.PONO,
WHWMD400.OBPR,
WHWMD300.LOCT,
WHWMD210.ZONE,
TCIBD001.KITM