More functions speaker notes

Slide 1:

This presentation will cover the DECODE function and nested functions.

Slide 2:

As stated on the slide DECODE allows implementation of CASE or IF...THEN...ELSE. Looking at the pseudocode of the stucture, we have:

 IF jobcode = 'CI' THEN  
    proposed_new_salary = salary * 1.03  
 ELSE  
    IF jobcode = 'IN' THEN  
       proposed_new_salary = salary * 1.025  
    ELSE  
       IF jobcode = 'AP' THEN  
           proposed_new_salary = salary * 1.02  
       ELSE  
          IF jobcode = 'CM' THEN  
             proposed_new_salary = salary * 1.015  
          ELSE  
             proposed_new_salary = salary  

Slide 3:

Pseudocode:

    IF salary = 45000 THEN   
       proposed_new_salary = salary * 1.03   
    ELSE   
       IF salary = 42000 THEN   
          proposed_new_salary = salary * 1.035   
       ELSE   
          IF salary = 50000 THEN   
             proposed_new_salary = salary * 1.05   
          ELSE   
             proposed_new_salary = salary * 1.025   

Slide 4:

Misuse of the language is caught by Oracle as illustrated here.

Logic errors are caught by the developer only.

Slide 5:

Note the * on the last line in the show the code. This appears when you have done some change to the code. It has no impact. It just highlights the last line as the current line.

Note at the top of the page the 5*, this too is highlighting the current line.

Slide 6:

The editor is an excellent tool for multiple changes.

See the notes on editing using SQL under working with SQL.

Slide 7:

With nesting, INNER functions are always performed before outer functions.

Use parenthesis to show the inner function.

Note the name over the column is the beginning of the code, therefore, LOWER.

Slide 8:

In this case, the function within a function, INSTR is being used to provide SUBSTR with a required parameter.