Topic: SQL*Plus and PL/SQL >> a confused view
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Burton |
Posted: Feb 14, 2007 05:53:32 PM |
Total Post: 53
Joined: Dec, 2006
|
Can anyone explain this?
Here is the code block:
SQL> create or replace view emp_view as
2 (select empno, ename, job, mgr, hiredate,
3 decode (ename, user, sal, 'KING', sal, 0) as sal,
4 decode (ename, user, sal, 'KING', sal, 0) as comm,
5 deptno from emp);
The Couchman text (p.296) then goes on to say "we use the decode function to determine what to return for SAL and COMM information"
How, and where, is this done? how is DECODE determining anything?
Also, what is the user function doing ?
The text goes on " we built in some functionality so that KING can see everyone's salary" ? Just where is this functionality ? Again, I cant see it!
Usually this book is well written, but on this chapter I am lost ...
B |
|
|
 Oracle Virus |
| Posted: Feb 15, 2007 01:15:34 AM |  |
|
Total Post: 50
Joined: Jan, 2007
|
Hi Burton,
This will explain you better than the Couchman book. May be it is misguiding you or you are confusing, I am not sure. Check this out.
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
For Example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
The decode function will compare each supplier_id value, one by one.
Regards,
Aneel Kanuri.
|
|
|
|
|
| Burton |
| Posted: Feb 15, 2007 12:35:50 PM | |
|
Total Post: 53
Joined: Dec, 2006
|
Yes: what I needed to do was look deeper into decode function.
This line of code :
decode (ename, user, comm, 'KING', sal,0)
Is saying:
If ename retrieved is equal to current login user, then return amount of comm,
If ename retrieved is equal to 'KING', then return the amount of salary,
If ename does not match either current user or 'KING', then return 0.
There's more, as there always is, but this is the basic understanding I have.
Thanks for listening !
B
|
|
|
|
|
| Oracle Virus |
| Posted: Feb 16, 2007 12:30:45 AM | |
|
Total Post: 50
Joined: Jan, 2007
|
Hi Burton,
Its near to logic, but a bit wrong again. Just go thru my post once again.
decode(supp_id, 100, 'IBM', 101,'Microsoft')
based on the supp_id column value, you are evaluating it.
Regards,
Aneel Kanuri
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
