More SQL functions

1. More SQL functions As usual,there is additional information in the speaker notes!

2. Decode function This command decodes the field jobcode and then performs a task. The results go in proposed_new_salary. In this case, if jobcode is CI then salary is multiplied by 1.03 and the result is stored in proposed_new_salary. SQL> SELECT name, jobcode, salary, 2 DECODE(jobcode, 'CI', salary *1.03, 3 'IN', salary *1.025, 4 'AP', salary *1.02, 5 'CM', salary *1.015, 6 salary) 7 proposed_new_salary 8 FROM first_pay; NAME JO SALARY PROPOSED_NEW_SALARY -------------------- -- --------- ------------------- Linda Costa CI 45000 46350 John Davidson IN 40000 41000 Susan Ash AP 25000 25500 Stephen York CM 42000 42630 Richard Jones CI 50000 51500 Joanne Brown IN 48000 49200 Donald Brown CI 45000 46350 Paula Adams IN 45000 46125 DECODE allows the implementation of a CASE or IF…THEN…ELSE structure.

3. Decode function DECODE of salary resulting in proposed_new_salary. Three salaries get specific raises all the rest are handled with salary *1.025. 1 SELECT name, jobcode, salary, 2 DECODE(salary, 45000, salary * 1.03, 3 42000, salary * 1.035, 4 50000, salary * 1.05, 5 salary * 1.025) 6 proposed_new_salary 7 FROM first_pay; NAME JO SALARY PROPOSED_NEW_SALARY -------------------- -- --------- ------------------- Linda Costa CI 45000 46350 John Davidson IN 40000 41000 Susan Ash AP 25000 25625 Stephen York CM 42000 43470 Richard Jones CI 50000 52500 Joanne Brown IN 48000 49200 Donald Brown CI 45000 46350 Paula Adams IN 45000 46350

4. Fixing problems with coding errors When I keyed this in, I forgot the comma after salary on the first line. This is the error that I got. SQL> SELECT name, jobcode, salary 2 DECODE(salary, 45000, salary * 1.03, 3 42000, salary * 1.035, 4 50000, salary * 1.05, 5 salary * .025) 6 proposed_new_salary 7 FROM first_pay; DECODE(salary, 45000, salary * 1.03, * ERROR at line 2: ORA-00923: FROM keyword not found where expected SQL> 1 1* SELECT name, jobcode, salary SQL> c/salary/salary, 1* SELECT name, jobcode, salary, SQL> / NAME JO SALARY PROPOSED_NEW_SALARY -------------------- -- --------- ------------------- Linda Costa CI 45000 46350 John Davidson IN 40000 1000 Susan Ash AP 25000 625 Stephen York CM 42000 43470 Richard Jones CI 50000 52500 Joanne Brown IN 48000 1200 Donald Brown CI 45000 46350 Paula Adams IN 45000 46350 To correct it, I first entered 1 to bring up line 1. Then I did a c of salary to salary, using c/salary/salary, Looking at the results I still have a problem. This time it is a logic problem. The default salary is multiplied by .025 instead of 1.025.

5. Fixing logic problem To fix the logic problem, I brought up line 5 where the error occurred and made the change. SQL> 5 5* salary * .025) SQL> c/.025/1.025 SQL> / NAME JO SALARY PROPOSED_NEW_SALARY -------------------- -- --------- ------------------- Linda Costa CI 45000 46350 John Davidson IN 40000 41000 Susan Ash AP 25000 25625 Stephen York CM 42000 43470 Richard Jones CI 50000 52500 Joanne Brown IN 48000 49200 Donald Brown CI 45000 46350 Paula Adams IN 45000 46350 8 rows selected. SQL> ; 1 SELECT name, jobcode, salary, 2 DECODE(salary, 45000, salary * 1.03, 3 42000, salary * 1.035, 4 50000, salary * 1.05, 5 salary * 1.025) 6 proposed_new_salary 7* FROM first_pay / executes the code ; shows the code

6. Editor Keying edit at the SQL prompt brings up notepad as a text editor. SQL> edit I made both of the changes in the editor. The comma was added and the .025 was changed to 1.025. I then save and close the editor. The code in Oracle is shown below. Wrote file afiedt.buf 1 SELECT name, jobcode, salary, 2 DECODE(salary, 45000, salary * 1.03, 3 42000, salary * 1.035, 4 50000, salary * 1.05, 5 salary * 1.025) 6 proposed_new_salary 7* FROM first_pay

7. SQL> SELECT * FROM first_pay; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 45000 1000 2222 John Davidson IN 25-SEP-92 40000 1500 3333 Susan Ash AP 05-FEB-00 25000 500 4444 Stephen York CM 03-JUL-97 42000 2000 5555 Richard Jones CI 30-OCT-92 50000 2000 6666 Joanne Brown IN 18-AUG-94 48000 2000 7777 Donald Brown CI 05-NOV-99 45000 2000 8888 Paula Adams IN 12-DEC-98 45000 2000 Nesting functions SQL> SELECT LOWER(SUBSTR(startdate,4,3))||LOWER(jobcode) 2 FROM first_pay; LOWER ----- janci sepin febap julcm octci augin novci decin First I will find the substr of the date field starting with character 4 and going for 3 characters. This finds the month. Then I convert it to lower case. The next function converts jobcode to lower case. These are concatenated together

8. Nested functions SQL> SELECT SUBSTR(stadr,(INSTR(stadr, ' '))) || ', ' || city 2 FROM donor; SUBSTR(STADR,(INSTR(STADR,' --------------------------- Elm St, Seekonk Benefit St, Providence Benefit St, Providence Main St, Fall River Oak St, Fall River Pine St, Fall River SUBSTR is operating on stadr. It needs a start point which is determined by using the INSTR operating on stadr to find the first space in stadr. This location gives SUBSTR its start point. I provide no end point so it takes the rest of the characters in substr. I then concatenate with a comma followed by a space and concatenate that with city. On the first example, the address is 123 Elm St. INSTR will find the space in position 4. Therefore, SUBSTR will start with the fourth character in stadr and go to the end.