CASE statement page 1
Let the list of all the models of PC is required along with their prices. Besides that, if the model is not on sale (not in PC table), in the place of price must be the text "Not available".
The list of all the PC models with their prices we can obtain running the query:
Missing prices will be replaced by NULL values:
The CASE statement helps us to get required text instead of NULLs:
Depending on defined conditions, the CASE statement returns one of the possible values. The condition in above example is the checking for NULL. If this condition is satisfied, the text "Not available" will be returned; otherwise (ELSE) it will be the price.
One principal moment is here. As a table is always the result of the SELECT statement, all values from any column must be of the same data type (having regard to implicit type conversions). Then, we cannot combine character constant with a price (numeric type) within a single column. That is the reason why we use the type conversion to the price column to reduce its type to character. As a result, we get