Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Basic SQL Reports in SQL Server

Introduction
Custom classifications of business objects
5. Fallback values
Custom grouping of business objects
Custom counting of business objects
Summary

Instruction

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:

SELECT 
  OrderID,
  CustomerID,
  ShipCountry,
  CASE
    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.

Exercise

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:

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