More sql functions
Download
1 / 8

More SQL functions - PowerPoint PPT Presentation


  • 128 Views
  • Uploaded on
  • Presentation posted in: General

More SQL functions. As usual,there is additional information in the speaker notes!. Decode function. This command decodes the field jobcode and then performs a task. The results go in proposed_new_salary.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'More SQL functions ' - zitomira


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
More sql functions

More SQL functions

As usual,there is additional information in the speaker notes!


More sql functions

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.


More sql functions

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


More sql functions

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.


More sql functions

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


More sql functions

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


More sql functions

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


More sql functions

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.


ad
  • Login