troubleshooting Question

Oracle 10g Evaluate count from partition

Avatar of JDCam
JDCam asked on
Oracle DatabaseSQL
2 Comments1 Solution498 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros