开发者

Omit null rows from query with conditional expressions

Is there a way to omit rows where both "CREATE" AND "DELETE" are NULL? Here's my query, any help would be much appreciated!

This is for a PostgreSQL database.*

SELECT 
jivedw_container.container_id as "ID", jivedw_day.year as "Year",
jivedw_day.week_of_year as "Week Of", jivedw_container.name as "Community",
jivedw_activity_agg_user_day.direct_object_type as "Object",
( jivedw_container.name 
                   || '@' 
                   || To_char(CURRENT_DATE- INTERVAL '138 day', 'mm/dd/yy') ) AS
"Object Link", 
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '20' THEN 1 ELSE NULL END)
as "CREATE",
sum(CASE WHEN jivedw_activity_agg_user_d开发者_运维技巧ay.activity_type = '30' THEN 1 ELSE NULL END)
as "DELETE"
FROM jivedw_activity_agg_user_day
INNER JOIN jivedw_day on jivedw_activity_agg_user_day.day_id = jivedw_day.day_id
INNER JOIN jivedw_container on jivedw_activity_agg_user_day.dw_container_id =
jivedw_container.dw_container_id
WHERE (jivedw_day.begin_ts BETWEEN CURRENT_DATE - INTERVAL '138 day' AND CURRENT_DATE - INTERVAL '132 day')
AND (jivedw_activity_agg_user_day.direct_object_type IN (1,2,37,38, 102, 1100))
GROUP BY
jivedw_container.container_id,
jivedw_container.name, 
jivedw_day.year,
jivedw_day.week_of_year,
jivedw_activity_agg_user_day.direct_object_type
ORDER BY jivedw_container.container_id, jivedw_container.name
;


SELECT * FROM (SELECT 

jivedw_container.container_id as "ID", jivedw_day.year as "Year",
jivedw_day.week_of_year as "Week Of", jivedw_container.name as "Community",
jivedw_activity_agg_user_day.direct_object_type as "Object",
( jivedw_container.name 
  || '@' 
  || To_char(CURRENT_DATE- INTERVAL '138 day', 'mm/dd/yy') ) AS "Object Link", 
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '20' THEN 1 ELSE NULL END)
as "CREATE",
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '30' THEN 1 ELSE NULL END)
as "DELETE"
FROM jivedw_activity_agg_user_day
INNER JOIN jivedw_day on jivedw_activity_agg_user_day.day_id = jivedw_day.day_id
INNER JOIN jivedw_container on jivedw_activity_agg_user_day.dw_container_id =
jivedw_container.dw_container_id
WHERE (jivedw_day.begin_ts BETWEEN CURRENT_DATE - INTERVAL '138 day' AND CURRENT_DATE - INTERVAL '132 day')
AND (jivedw_activity_agg_user_day.direct_object_type IN (1,2,37,38, 102, 1100))
GROUP BY
jivedw_container.container_id,
jivedw_container.name, 
jivedw_day.year,
jivedw_day.week_of_year,
jivedw_activity_agg_user_day.direct_object_type
ORDER BY jivedw_container.container_id, jivedw_container.name
;) as SUB_QUERY WHERE SUB_QUERY.CREATE IS NOT NULL AND SUB_QUERY.DELETE is NOT NULL

This should work

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜