Link to home
Start Free TrialLog in
Avatar of JDCam
JDCam

asked on

Oracle 10g Evaluate count from partition

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDCam
JDCam

ASKER

Perfect .. Thx!