Oracle 10g  Evaluate count from partition

JDCam
JDCam used Ask the Experts™
on
Experts,
I wrote this little query to show me how many unique instances of BREAK I have per ITEM. Output looks good.  I want to add 'WHERE NUM > 1, to show only where there is more than 1 instance.  It's not liking my syntax when I attempt to add this.
Please help.

Select
  ITEM,
  BREAK,
  count(BREAK)OVER(PARTITION BY ITEM) NUM
from (
Select INVT_LEV1 ITEM, INVT_QTY_BKD_FACT BREAK
from C_INVT
where Cust_Code = 'GMAFH'
and COMP_CODE = 'W1'
and ON_HAND_QTY > 0
GROUP BY INVT_LEV1, INVT_QTY_BKD_FACT
order by INVT_LEV1)
where NUM > 1    <----------------- this line is giving error when present !!!!
group by ITEM, BREAK
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
try this:

select item, break, num from
(
Select
  ITEM,
  BREAK,
  count(BREAK)OVER(PARTITION BY ITEM) NUM
from (
Select INVT_LEV1 ITEM, INVT_QTY_BKD_FACT BREAK
from C_INVT
where Cust_Code = 'GMAFH'
and COMP_CODE = 'W1'
and ON_HAND_QTY > 0
GROUP BY INVT_LEV1, INVT_QTY_BKD_FACT
order by INVT_LEV1)
group by ITEM, BREAK
)
where NUM > 1

Author

Commented:
Perfect .. Thx!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial