How do I use a WHERE clause in this query?

How do I use a WHERE clause in this query?
I wish to use a where on the COUNT column
Thanks


select b.item_id as "INV_MAST_item_id",a.alternate_code as "ALTERNALTE_CODE_alternate_code",
         ROW_NUMBER() OVER ( PARTITION BY a.inv_mast_uid
                             ORDER BY a.inv_mast_uid DESC
                           ) rownum,
            COUNT(*) OVER (PARTITION BY a.alternate_code) counts , a.delete_flag
from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid

Open in new window

Richard GouetteIT ManagerAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
You can use WITH.... but you may also the more traditional approach (a simple "subquery").
select
*
from (
      SELECT
            b.item_id AS "INV_MAST_item_id"
          , a.alternate_code AS "ALTERNALTE_CODE_alternate_code"
          , ROW_NUMBER() OVER (PARTITION BY a.inv_mast_uid
                                ORDER BY a.inv_mast_uid DESC
                                ) rownum
          , COUNT(*) OVER (PARTITION BY a.alternate_code) counts
          , a.delete_flag
      FROM alternate_code a
      INNER JOIN inv_mast b ON b.inv_mast_uid = a.inv_mast_uid
      ) d
where counts > 1
;

Open in new window

What you CANNOT do, when you use OVER(...), is to refer to that result by a column alias in a single select statement. By using an extra select "layer" the column alias will be recognized in that subsequent where clause.

{+edits}
Column aliases NOT working in a where clause can be confusing, but the cause of this is due to the fact that a select clause is performed AFTER the FROM and WHERE clauses. So as it gets executed the query looks more like this:

FROM alternate_code a INNER JOIN inv_mast b ON b.inv_mast_uid = a.inv_mast_uid
WHERE [counts] > 1 --<< oops! "counts" does not yet exist

SELECT ... COUNT(*) OVER (PARTITION BY a.alternate_code) counts

& that's why the extra "layer" of query allows use of the column alias
1
 
_agx_Commented:
Wrap it in a WITH clause. Then you can use the aggregate in the outer WHERE clause

Replace "WHERE  counts = @something" with whatever operation you want to perform on the "counts" column.

;WITH data
AS
(
select b.item_id as "INV_MAST_item_id",a.alternate_code as "ALTERNALTE_CODE_alternate_code",
         ROW_NUMBER() OVER ( PARTITION BY a.inv_mast_uid
                             ORDER BY a.inv_mast_uid DESC
                           ) rownum,
            COUNT(*) OVER (PARTITION BY a.alternate_code) counts , a.delete_flag
from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid
)
SELECT *
FROM    data
WHERE  counts = @something

Open in new window

0
 
Richard GouetteIT ManagerAuthor Commented:
I get:

Invalid column name 'counts'.

Open in new window

It doesn;t like me tryign to use that as a column, clearly..
;(
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
_agx_Commented:
Which dbms?  Works fine with SQL Server. Note, I fudged the data types for demo only

http://sqlfiddle.com/#!18/1ecb1/2/0
0
 
Richard GouetteIT ManagerAuthor Commented:
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)   Feb  4 2011 11:27:06   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

hmm strange

I tried:

from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid
WHERE counts = 1

Open in new window

0
 
_agx_Commented:
The "counts" column can't be used in a WHERE clause of the original query because it doesn't exist yet.  You need to wrap it in a WITH clause, like in my example:

http://sqlfiddle.com/#!18/1ecb1/2/0
0
 
Richard GouetteIT ManagerAuthor Commented:
Excellent.
Many thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.