simplecaseexpression.ppt

Conditional Expressions What Will I Learn? Compare and contrast the DECODE and CASE functions Correctly uses the DECODE and CASE functions Construct and execute two methods for implementing IF-THEN-ELSE conditional logic Why Learn It? Being able to make decisions is essential in data modeling. Modelers have to decide which business functions need to be modeled and which do not. The data-modeling process requires the designers to analyze information to identify entities, resolve relationships, and select attributes. A typical decision could be, IF a business needs to track data over time, THEN time may need to be an entity or ELSE time should be an attribute. Why Learn It? This decision process is not much different from those we make in everyday life. Think of the last time you had to make an if-then-else kind of decision. IF I get my homework done before 9:00 p.m., I can watch television, ELSE I cant watch television. In SQL, these kinds of choices involve conditional processing methods. Knowing how to use conditional processing makes decision making to get the data you want easier. simple_case_expression The CASE expression basically does the work of an IF-THEN-ELSE statement. Data types of the CASE, WHEN, and ELSE expressions must be the same. The syntax for CASE is: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END simple_case_expression SELECT id, loc_type,rental_fee, CASE loc_type WHEN Private Home THEN No Increase WHEN Hotel THEN Increase 5% ELSE rental_fee END AS REVISED_FEES FROM d_venues; simple_case_expression SELECT id, loc_type, rental_fee, CASE loc_type WHEN Private Home THEN 500 WHEN Hotel THEN 1000 ELSE 300 END AS REVISED_FEES FROM d_venues; searched_case_expression SELECT last_name,salary, (CASE WHEN salary5000 THEN Low ????WHEN salary10000 THEN Medium