More functions speaker notes
This presentation will cover the DECODE function and nested functions.
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
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
Misuse of the language is caught by Oracle as illustrated here.
Logic errors are caught by the developer only.
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.
The editor is an excellent tool for multiple changes.
See the notes on editing using SQL under working with SQL.
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.
In this case, the function within a function, INSTR is being used to provide SUBSTR with a required parameter.