question

mallik454 avatar image
mallik454 asked

How to avoid displaying null values from below sql query

I am getting null values in below query, i dont want to show those null values. how to do SELECT JOB, SUM(DECODE(DEPTNO,10,SAL)) DEPT10, SUM(DECODE(DEPTNO,20,SAL)) DEPT20, SUM(DECODE(DEPTNO,30,SAL)) DEPT30, SUM(SAL) TOTAL FROM EMP GROUP BY JOB;
oracleoracle-sql-developer
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have both SQL Server and Oracle in the tag. Which is this query for?
1 Like 1 ·
Comment in response to @SirSQL's answer leads me to believe that this is an Oracle query. I've retagged.
0 Likes 0 ·

1 Answer

· Write an Answer
SirSQL avatar image
SirSQL answered
When you say that you don't want to show the null values I'm assuming that you don't want to show the row at all if and of the sum values is a null. If that's the case you should just add a where clause to eliminate those rows: SELECT JOB, SUM(DECODE(DEPTNO,10,SAL)) DEPT10, SUM(DECODE(DEPTNO,20,SAL)) DEPT20, SUM(DECODE(DEPTNO,30,SAL)) DEPT30, SUM(SAL) TOTAL FROM EMP WHERE SUM(DECODE(DEPTNO,10,SAL)) IS NOT NULL AND SUM(DECODE(DEPTNO,20,SAL)) IS NOT NULL AND SUM(DECODE(DEPTNO,30,SAL)) IS NOT NULL GROUP BY JOB; Alternatively if you meant you wanted to show something other than a null value for those records that are currently null: SELECT JOB, ISNULL(SUM(DECODE(DEPTNO,10,SAL)),0) DEPT10, ISNULL(SUM(DECODE(DEPTNO,20,SAL)),0) DEPT20, ISNULL(SUM(DECODE(DEPTNO,30,SAL)),0) DEPT30, ISNULL(SUM(SAL),0) TOTAL FROM EMP GROUP BY JOB;
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Both the answers are not working. In the second case, i hope we need to use NVL instead of ISNULL. But the first one is throwing error based on Group by. ORA-00934: group function is not allowed here. Please check it once from ur end. Thanks Mallik
0 Likes 0 ·
Ah, so it's an Oracle problem...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.