CODEWITHSUNDEEP
sqlsql-serverexcelnullreturn-value
vancouver3
vancouver3

Reputation: 49

SQL Query Excel - return "0" if value not found

I have a simple query that uses the following command text to return values from a SQL Server to an Excel sheet. However, when the Material value is not found in the Material_Location table, it simply omits it.

How can I modify the following code to return the value of On_Hand_Qty as "0", if the Material is not found in the Material_Location table 

    SELECT
      SO_Detail.Sales_Order,
      SO_Detail.SO_Line,
      SO_Detail.Material,
      SO_Detail.Order_Qty,
      Material_Location.On_Hand_Qty
     FROM
      SO_Detail
      INNER JOIN Material_Location ON SO_Detail.Material = Material_Location.Material
    ORDER BY
      SO_Detail.Sales_Order,
      SO_Detail.SO_Line

Thanks

Upvotes: 1

Views: 275

Answers (1)

user359135
user359135

Reputation:

Using a left outer join instead of inner join will allow you to still get records from SO_Detail even when no matching record exists in Material_Location.

COALESCE will go through each parameter passed to it until it finds one that is not null 

SELECT
  sd.Sales_Order,
  sd.SO_Line,
  sd.Material,
  sd.Order_Qty,
  COALESCE(ml.On_Hand_Qty,0) as On_Hand_Qty
 FROM
  SO_Detail sd
  LEFT OUTER JOIN Material_Location ml ON sd.Material = ml.Material
ORDER BY
  sd.Sales_Order,
  sd.SO_Line

Upvotes: 2

Related Questions