| Outer join with variable [message #316385] |
Thu, 24 April 2008 21:47  |
ekteh27
Messages: 7
Registered: February 2008
Location: Malaysia |
Junior Member |
|
|
Hi,
I had a question regarding outer join between a table and a variable.
Refer to oracle SQL laguage reference:
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise
Oracle returns only the results of a simple join.
But when I try to perform following statement:
select sal
from emp
where empno(+) = 9999
Where 9999 is not exist empno, base on above description, I expect the statement will return a null value, but it return no record found.
Then I amend the statement to
select Count(sal)
from emp
where empno(+) = 9999
System return 0 as well.
But when i amend the statement to
select Max(sal)
from emp
where empno(+) = 9999
System return null.
My question is this a oracle bug or the document not documented correct?
Thanks
|
|
|
| Re: Outer join with variable [message #316387 is a reply to message #316385 ] |
Thu, 24 April 2008 22:18  |
flyboy
Messages: 441
Registered: November 2006 |
Senior Member |
|
|
Correct.
Maybe it is not stated explicitly, but the join operator (+) is considered only for joins. The query does not join anything, so it is meaningless. The (+) operator is ignored in all three cases.
Just try to "rewrite" the query to ANSI outer join syntax...
Aggregate functions without GROUP BY clause return always one row - aggregated value for all source rows.
[Edit: typo]
[Updated on: Thu, 24 April 2008 22:19]
|
|
|
|
|
|
| Re: Outer join with variable [message #316402 is a reply to message #316400 ] |
Fri, 25 April 2008 00:33 |
Michel Cadot
Messages: 15243
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | This might save your time to reply the same sentence to every posting topic.
|
No as nobody will read and even less try to pass it.
| Quote: | If you feel the question i posted show that I not expert in SQL,
|
Above all it is your answer that may me think you are not an expoert.
By the way, this question has been asked several times and it is a basic one about outer join (read carefully the word join as flyboiy pointed to your attention).
Regards
Michel
|
|
|
|
| Re: Outer join with variable [message #316420 is a reply to message #316414 ] |
Fri, 25 April 2008 01:49 |
Michel Cadot
Messages: 15243
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | nothing personal, just discuss openly. Hope that you won't mind.
|
I don't mind we discuss about this on moderator forum.
| Quote: | let say someone has pass exam 1Z0-001 and 1Z0-147 and work with pl/sql for more than 5 years.
|
This is not a criteria of expert. How many are in this case and don't know bulk operations? How many write their PL/SQL like we wrote it in version 7? How many use "when others then dbms_output..." or the like? How many commit/rollback inside a procedure? How many use "execute immediate" to just make a static DML? How many commit after a DDL? How many wrongly use triggers and/or get mutating error?...
Expert is someone that at least understand the principles before knowing the most high level features.
Just my opinion.
Regards
Michel
|
|
|
| Re: Outer join with variable [message #316421 is a reply to message #316385 ] |
Fri, 25 April 2008 01:56 |
flyboy
Messages: 441
Registered: November 2006 |
Senior Member |
|
|
> since the document they release also give the wrong result.
May I know, which document are you pointing to? The part you quoted, is related to joins (which is also stated here - see the reference to B and A tables), so I find it correct.
I am afraid, that if every (im)possibility would be stated there, it would be indecipherable.
I also do not think this is an expert question. Only overlooked the forum name when replied from site home (new posts section).
[Edit: the first paragraph enhanced]
[Updated on: Fri, 25 April 2008 01:59]
|
|
|
|
| Re: Outer join with variable [message #316454 is a reply to message #316421 ] |
Fri, 25 April 2008 03:04 |
ekteh27
Messages: 7
Registered: February 2008
Location: Malaysia |
Junior Member |
|
|
Hello Flyboy,
Ops! Sorry, is my eyesight mistake, i had read wrongly the paragraph. Really sorry about that and thanks for your respond again!!! you are so kind!!
Regards,
EK
|
|
|
]
Members
Search
Help
Register
Login
Home
