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

Question

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

Is this what you're looking for? ifnull(Material_Location.On_Hand_Qty,0) — tonypdmtr
– tonypdmtr, Commented Nov 7, 2017 at 16:51
There is a lot of ways to do it, using CASE, ISNULL, IIF, COALESCE — Ilyes
– Ilyes, Commented Nov 7, 2017 at 16:53
@Sami - before that we need Left Outer Join to get the non matching record — Pரதீப்
– Pரதீப், Commented Nov 7, 2017 at 16:54

Pரதீப் · Accepted Answer · 2017-11-07 16:57:38Z

2

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

edited Nov 7, 2017 at 16:57

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

answered Nov 7, 2017 at 16:53

user359135

Sign up to request clarification or add additional context in comments.

3 Comments

user359135 Over a year ago

@Pரதீப் not sure what you mean?

Pரதீப் Over a year ago

@gordatron - Query looks lot better now :) +1

user359135 Over a year ago

@Pரதீப் i was wondering if that was what you meant :D all those characters just too much for you? if i had not copied and pasted i would not have been able to bring myself to type that all out XD

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related