Omit null rows from query with conditional expressions

Question

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_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
;

DonCallisto · Accepted Answer · 2011-09-17 08:07:06Z

1

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

answered Sep 17, 2011 at 8:07

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sheebs Over a year ago

Had to put quotation marks around CREATE AND DELETE, but it worked! Thanks for your help!

DonCallisto Over a year ago

Yes, I missed those when I type onto keyboard. Regards

sheebs Over a year ago

Actually, it's as SUB_QUERY WHERE SUB_QUERY."CREATE" IS NOT NULL OR SUB_QUERY."DELETE" is NOT NULL

Collectives™ on Stack Overflow

Omit null rows from query with conditional expressions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related