Topic: SQL*Plus and PL/SQL >> Query on Outer join
|
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.
|
|
|
|
| Title: Query on Outer join |
| vikram |
Posted: Dec 28, 2005 07:56:18 AM |
Total Post: 2
Joined: Dec, 2005
|
Hi all,
I have a doubt, its written below, pls get me solved
Inner join:
SELECT D.DEPTNAME, E.EMPNAME
FROM DEPARTMENT D, EMPLOYEE E
WHERE trim(E.DEPTNAME) = D.DEPTNAME
AND E.EMPNAME = 'ABC'
TO FETCH ALL THE VALUES FROM DEPARTMENT TABLE BUT ONLY MATCHING
VALUES FROM EMPLOYEE TABLE, I THINK WE MUST DO AN OUTER JOIN ON
EMPLOYEE TABLE(OPTIONAL TABLE). SO, QUERY MUST BE SOME THING LIKE AS SHOW BELOW [PLS CORRECT IF I AM WORNG]
SELECT D.DEPTNAME, E.EMPNAME
FROM DEPARTMENT D, EMPLOYEE E
WHERE trim(E.DEPTNAME)(+) = D.DEPTNAME
AND E.EMPNAME(+) = 'ABC'
BUT This, is giving me "invalid relational operator"...CAN any one guide me with the query, to get the required results.
please do remember not to use LEFT OUTER JOIN or RIGHT OUTER JOIN, while rewriting the query.
Explaination to rewritten query is appreciated.
Thanks in Advance,
Vikram Hegde |
|
|
 vikram |
| Posted: Dec 29, 2005 12:04:12 AM |  |
|
Total Post: 2
Joined: Dec, 2005
|
FYI,
The outer join operator needs to go with the field name, not with the result of the function. So, just move the operator inside the parens for the TRIM function like:
SELECT d.deptname, e.empname
FROM department d, employee e
WHERE TRIM(e.deptname(+)) = d.deptname AND
e.empname(+) = 'ABC'
Thanx,
Vic's
|
|
|
|
|
| Vigyan |
| Posted: Dec 29, 2005 08:09:29 AM | |
|
Total Post: 387
Joined: May, 2001
|
Thanks for sharing solution with rest of us.
Vigyan
|
|
|
|
|
| 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 | 387 |
| Vinoth Kumar | 379 |
| Gopu Gopi | 350 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
