Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Custom classifications of business objects
5. Fallback values
Custom grouping of business objects
Custom counting of business objects


So, orders shipped outside the USA and Canada had NULL ShippingCost values. This is because we didn't specify the column value for other countries.

Northwinds' shipping cost for countries other than the US and Canada is $10.00. How can we show that in the report? Take a look:

    WHEN ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN 0.0
    ELSE 10.0
  END AS ShippingCost
FROM Orders
WHERE OrderID BETWEEN 10720 AND 10730;

We enriched our CASE WHEN construction with an ELSE. The ELSE is executed when no other conditions are satisfied. Thanks to this, all other countries will get a column value of 10.0 instead of NULL.


We want to show the following basic customer information (the Customers table):

  1. CustomerID
  2. CompanyName
  3. Country
  4. Language

The value of the Language column will be decided by the following rules:

  • N'German' for companies from Germany, Switzerland, and Austria.
  • N'English' for companies from the UK, Canada, the USA, and Ireland.
  • N'Other' for all other countries.

Stuck? Here's a hint!

Use the following template in the SELECT clause:

  WHEN Country IN (...) THEN N'German'
  WHEN Country IN (...) THEN N'English'
  ELSE N'Other'