CODEWITHSUNDEEP
sqlnullreplace
ray
ray

Reputation: 11

How to replace null into 0 in a complex query

I would like to replace the total value to 0 when it is null

Here is the query:

SELECT DISTINCT(location),  (
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

Upvotes: 1

Views: 20931

Answers (6)

Pragme
Pragme

Reputation: 21

If you are using SQL server, the following are the 3 ways that can be used to replace a null with any user defined substitute value. 1. ISNULL 2. COALESCE 3. CASE

This question is also asked in one of the interviews I attended. Here is the link for an article with examples. By the way, there's also a video on the same in this article.

Different ways to replace NULLS in SQL Server

Upvotes: 2

Alin P.
Alin P.

Reputation: 44346

I would like to replace the total value to 0 when it is null

This is an impossible situation because:

The COUNT function always returns an integer. The result cannot be NULL!

As for coalescing an expression to a certain default in case it is NULL there are functions that do this in all major databases (ex.: COALESCE, NVL, ISNULL, IFNULL). The typical use is 

FUNCTION_NAME(ExpressionThatMayBeNULL, DefaultWhenNull)

For specifics you should consult you database manufacturers documentation (you can find it online).

Upvotes: 3

dparker
dparker

Reputation: 1082

You can use the ISNULL function.

Here is how you will use the function (for SQL Server).

ISNULL(columnName, 0)

Upvotes: 6

eumiro
eumiro

Reputation: 212835

What DB system are you using?

  • SQL-Server, MySQL: IFNULL(value, 0)
  • Oracle: NVL(value, 0)
  • PostgreSQL: COALESCE(value, 0)

Upvotes: 1

asawyer
asawyer

Reputation: 17808

As dparker said, ISNULL(...) will work for some types of sql, though the name of the function can vary among database providers.

The function in IBM DB2 is called COALESCE(...), and in Oracle SQL it is NVL(...) for example.

This may be usefull

http://www.w3schools.com/sql/sql_isnull.asp

Upvotes: 2

user111013
user111013

Reputation:

COALESCE will do this in PL/SQL (Oracle) and T-SQL (SQL Server)

Syntax is COALESCE(field1, field2[, fieldN]) - it will select the first column from the left to have a non-null value.

Modifying the query you had: 

SELECT DISTINCT(location),  COALESCE((
    SELECT  Count(a.location) as total
    FROM table_fo a   
    LEFT JOIN  table_info b ON a.TRADEID = b.TRADEID AND   a.asofdate = b.asofdate
    WHERE (b.TERMSTATUS <> 'TRAN' OR b.TERMSTATUS is NULL)  AND b.asofdate = '20110105' AND a.location = pfo.location
 GROUP BY a.LOCATION  
),0)   AS total   
FROM table_fo  pfo
WHERE asofdate = '20110105';

Upvotes: 2

Related Questions