Introduction
Custom classifications of business objects
Custom grouping of business objects
Custom counting of business objects
Instruction
So, orders shipped outside the USA and Canada had NULL shipping_cost 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
order_id,
customer_id,
ship_country,
CASE
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
ELSE 10.0
END AS shipping_cost
FROM orders
WHERE order_id 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 (from the customers table):
customer_idcompany_namecountrylanguage
The value of the language column will be decided by the following rules:
'German'for companies from Germany, Switzerland, and Austria.'English'for companies from the UK, Canada, the USA, and Ireland.'Other'for all other countries.
Stuck? Here's a hint!
Use the following template in the SELECT clause:
CASE WHEN country IN (...) THEN 'German' WHEN country IN (...) THEN 'English' ELSE 'Other' END
