Menu
I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can select into a table variable but it feels a little dirty.
SELECT iar.Description, iai.Quantity, iai.Quantity * rpl.RegularPrice as 'Retail', iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes' I would like the Quantity and RegularPrice to default to zero if possible.
265
For Oracle you can use: NVL(columnName,deafultValue) :- NVL is used to convert a null value to a default value in the query output. eg. If you want to replace null values with 'NA' then use something like this.
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
A left outer join displays the null value in the first table. The results make it difficult to distinguish a null in the data from a null that represents a failure to join. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
As we have seen from the above examples joining NULL values does not work. Even though you have two NULL values SQL Server does not treat these as the same value. Internally a value of NULL is an unknown value and therefore SQL Server does not equate an unknown value being equal to another unknown value.
That's as easy as
IsNull(FieldName, 0) Or more completely:
SELECT iar.Description, ISNULL(iai.Quantity,0) as Quantity, ISNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail', iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes' In case of MySQL or SQLite the correct keyword is IFNULL (not ISNULL).
SELECT iar.Description, IFNULL(iai.Quantity,0) as Quantity, IFNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail', iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
