compress null values in sql server case statement
-
Hi All,
I have a case statement subquery, and it returns a resultset containing many null values which I convert into 0. My question is how do I get rid of the null values?
Here is the SQL
SELECT prj.f1,prj.f3,prj.4
FROM (SELECT f1
SUM(CASE WHEN f1 LIKE ' '3%' THEN invoiced ELSE 0 END) AS 'Revenue',
SUM(CASE WHEN f1 LIKE ' '4%' THEN invoiced ELSE 0 END) AS 'Revenue2'
FROM prj2 GROUP BY f1,f2) AS prj
My resultset returns something like this:
f1 f3 f4 salesteam 0 0 salesteam 0 0 salesteam 1 1 salesteam 0 0 salesteam 0 2 salesteam 0 0 salesteam 1 4 salesteam 0 0 How can I suppress what's in red??
Thanks all
-
I think you may have copied your query incorrectly. First, should there be a comma after f1 in your subselect? Also, where do the values prj.f3 and prj.f4 come from?
I have two suggestions, (assuming prj.f3 and prj.f4 exist somewhere, (maybe they are Revenue and Revenue2)) because I am not sure if you want the rows omitted if both values equal zero, or the row omitted if only one value equals zero...
SELECT prj.f1, prj.f3, prj.4
FROM( SELECT f1 SUM(CASE WHEN f1 LIKE '3%' THEN invoiced ELSE 0 END) AS 'Revenue',
SUM(CASE WHEN f1 LIKE '4%' THEN invoiced ELSE 0 END) AS 'Revenue2'
FROM prj2 GROUP BY f1,f2) AS prj
WHERE prj.f3 > 0
AND prj.f4 > 0
SELECT prj.f1, prj.f3, prj.4
FROM( SELECT f1 SUM(CASE WHEN f1 LIKE '3%' THEN invoiced ELSE 0 END) AS 'Revenue',
SUM(CASE WHEN f1 LIKE '4%' THEN invoiced ELSE 0 END) AS 'Revenue2'
FROM prj2 GROUP BY f1,f2) AS prj
WHERE( prj.f3 > 0 OR prj.f4 > 0)
I wasn't born stupid - I had to study.
-
Hi,
What happens if I have three revenues columns and some values contain negatives. You still have to report those values otherwise your results will be skewd. I tried using the condition you mentioned however when I excluded one of the revenue columns, the resultset returned no records.
Thanks,
Joe
-
These are the kinds of business rules that are better stated up front.
Instead of making your Revenue's > 0, try making them <> 0.
(It sounds like I will need more information about this query... Is the original posting the correct query, because it looked very odd to me...).
I wasn't born stupid - I had to study.
-
Hi,
Unfortunately I can only give pseudo code. IT policy here. However once you mentioned to add <> 0 I included the column in question into my where clause and everything is working accordingly.
Concerning business rules, it's a little difficult because of the environment here. Business rules mean accountability and if there are no BR then there's little to no accountability, I live in a scope creep environment. I sometimes am pulling my hair trying to get definitive answers (or a semblance sp).
Thanks for all your help
-
Been there as have most of us, (on many, many of my Contracts - especially Government...) and I feel for ya!!
Oil well - sleep well tonight and enjoy the heck out of the weekend!
I wasn't born stupid - I had to study.
-
Thanks a bunch. Being a contractor or consultant eases the pain a little bit. You have a great weekend too
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply