Switch to: V11V10V9V8V7V6V5

Table of Contents

CASЕ

This instruction gets the possibility of fork organizing while value computing. When DBMS meets this instruction it computes first condition and when it is true the first resulting expression is calculated. If the first condition false the second one is checked and if it is true the second resulting expression is executed and so on.

Divide clients on three categories in accordance with the sums of their credit limit.

	SELECT company, CASE WHEN credit_limit > 60000 THEN ‘A’
				WHEN credit_limit > 30000 THEN ‘B’
				ELSE ‘C’
				END
	FROM customers

Any expression could be used as CASE instruction result. It is not necessary all checks placed in WHEN statements were analogical, moreover CASE operation may be present not only in SELECT statement.

If you need to compare the result of some expression with several values ( more often literal values) then instead of repetition of the conditions like checked_expression = value

In every WHEN statement, you can place the checked expression just after CASE keyword in order to calculate it only once for each record and just compare it ‘s value to the WHEN statements check-values.

Show the list of all company offices with their managersnames and names, cities and states they are located in.

	SELECT name, city, CASE office WHEN 11 THENNEW York’
					       WHEN 12 THEN ‘ Illinois’
					       WHEN 13 THEN ‘Georgia’
				     END
	FROM 
		offices, salesreps
	WHERE 
		MGR = empl_num 

COALESCE

This is special kind of “CASE” operator. The query could be simplified in some cases with using COALESCE

Show the report about employees and their sales plans. If employee has no plan - show his real amount of sales , otherwise show zero.

	SELECT 
		name, COALESCE ( quota, sales, 0.00 )
	FROM 
		salesreps

Performing this query DBMS calculates the first expression in the COALESCE list. If its value is not equal to NULL, it becomes the result of the whole COALESCE command. In opposite case DBMS switches to the second expression in the list and checks whether it is equal to NULL. If not it returns as a result otherwise DBMS moves to the third expression and so on till not-NULL value will be found.

NULL IF

In some cases absent data are represented not as NULL values but as special codes which can’t be interpreted as data. If you need to reveal such codes and to change them on NULL, you can make this with NULL IF operation. When DBMS meets such instruction, the first expression is analyzing ( usually it is the field name ) and compares its value with the value of the second expression ( usually code one being the absent data ). If they are equal NULL becomes the result of NULL IF function, in another case - the first expression value ( becomes the result ).

In the given query zero means absence of office number:

	SELECT city, SUM ( salesreps.sales )
	FROM offices, salesreps
	WHERE office = NULL IF ( rep_office, 0 )
	GROUP BY city