use column alias which have over(partition) in where clause

Question

Hi can anyone help me in this query, how to add total_pallet_id >= 80 in where statements.

Thanks for your kind help, much appreciate.

Answer 1

You need to use a subquery or CTE:

with t as (
      < your query here >
     )
select t.*
from t
where total_pallet_id >= 80;

Answer 2

SELECT * FROM
(
    select To_Char(c.last_event_date,'MM/DD/YYYY') as lastDate, 
    a.phase, a.kitting_code, a.carton_id || a.kitting_code as StorePalletID,
    SUM(COUNT(*)) OVER(PARTITION BY a.carton_id ||  a.kitting_code) AS 
    total_pallet_id,
    c.cass_id,c.last_event_date
    from ods.carton_master a join ods.carton b on  a.carton_id = b.carton_id 
    join ods.cass_event_master_vw c on b.cass_id = c.cass_id 
    where a.carton_id like 'S%' 
    and a.complete = 'Y'
    and c.last_event_date >= '2018-04-14 15:13:50' AND c.last_event_date < 
    '2018-05-22 15:13:50'
    Group by lastDate, a.phase, a.kitting_code, a.carton_id || a.kitting_code, 
    c.cass_id,c.last_event_date        
) t
WHERE total_pallet_id>=80
ORDER BY lastDate,  phase, kitting_code