CODEWITHSUNDEEP
sqlsql-server
Moses E
Moses E

Reputation: 35

To make 0 if the value is null in sql server

In the below query i am select the product with currentstock .Now i want to replace 0 if the currrent stock is null.Pls help me to do this.

SELECT   p.ProductID,
       p.ProductName,
       (SELECT ISNULL( CurrentStock,0.00) 
        FROM   Productstatus PS
        WHERE  PS.ProductID =p.ProductID
               AND PS.LocationID = 1
               AND PS.StatusDateTime= '2014-08-27'
               and PS.productid=p.productid) CurrentStock
FROM   Product P
LEFT OUTER JOIN LocationProductMap LMP ON LMP.ProductID=P.ProductID
WHERE LMP.ProductInFlow=1

Upvotes: 0

Views: 73

Answers (3)

knkarthick24
knkarthick24

Reputation: 3216

Single COALESCE will do the job in your query. Also it is more effective than ISNULL function.

SELECT   p.ProductID,
   p.ProductName,
   COALESCE((SELECT CurrentStock 
    FROM   Productstatus PS
    WHERE  PS.ProductID =p.ProductID
           AND PS.LocationID = 1
           AND PS.StatusDateTime= '2014-08-27'
           and PS.productid=p.productid),0) CurrentStock
FROM   Product P
LEFT OUTER JOIN LocationProductMap LMP ON LMP.ProductID=P.ProductID
WHERE LMP.ProductInFlow=1

Upvotes: 0

King_Fisher
King_Fisher

Reputation: 1203

you can use

Select case when (CurrentStock) is null then 0 else (CurrentStock) end from table

Upvotes: 0

Sabin B
Sabin B

Reputation: 293

SELECT   p.ProductID,
   p.ProductName,
   ISNULL((SELECT ISNULL( CurrentStock,0.00) 
    FROM   Productstatus PS
    WHERE  PS.ProductID =p.ProductID
           AND PS.LocationID = 1
           AND PS.StatusDateTime= '2014-08-27'
           and PS.productid=p.productid),0) CurrentStock
FROM   Product P
LEFT OUTER JOIN LocationProductMap LMP ON LMP.ProductID=P.ProductID
WHERE LMP.ProductInFlow=1

Upvotes: 2

Related Questions