Home » SQL & PL/SQL » SQL & PL/SQL » Outer join with variable (Oracle 10G)
Outer join with variable [message #316385] Thu, 24 April 2008 21:47 Go to next message
ekteh27
Messages: 10
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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]

Report message to a moderator

Re: Outer join with variable [message #316388 is a reply to message #316385] Thu, 24 April 2008 22:33 Go to previous messageGo to next message
ekteh27
Messages: 10
Registered: February 2008
Location: Malaysia
Junior Member
Thanks Flyboy. Smile
Re: Outer join with variable [message #316392 is a reply to message #316388] Thu, 24 April 2008 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time read Not an EXPERT? Post in the NEWBIES forum, NOT here and post in this forum ONLY if you answer YES to the 3 questions and obviously you CAN'T answer yes to the first one.

Regards
Michel
Re: Outer join with variable [message #316400 is a reply to message #316385] Fri, 25 April 2008 00:16 Go to previous messageGo to next message
ekteh27
Messages: 10
Registered: February 2008
Location: Malaysia
Junior Member
Hi Michel,
If you feel the question i posted show that I not expert in SQL, then must be oracle staff issue since the document they release also give the wrong result.
I suggest you put some questions to test the person who post new topic here to make sure whether they are expert or not,
since I feel myself am expert but u feel i'm not. Wink
This might save your time to reply the same sentence to every posting topic.

Thanks,
EK
Re: Outer join with variable [message #316402 is a reply to message #316400] Fri, 25 April 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 #316414 is a reply to message #316402] Fri, 25 April 2008 01:00 Go to previous messageGo to next message
ekteh27
Messages: 10
Registered: February 2008
Location: Malaysia
Junior Member
Quote:
No as nobody will read and even less try to pass it.

I feel that you are prejudice. I agree that not all people will follow but still will have group of people take it seriously.

But actually you shall consider to come out a criteria what is so call expert in SQL, let say someone has pass exam 1Z0-001 and 1Z0-147 and work with pl/sql for more than 5 years. Does he consider an expert? Or maybe the one know everything like you only consider expert and qualify to post topic here.

p/s: nothing personal, just discuss openly. Hope that you won't mind. Smile

Thanks
EK
Re: Outer join with variable [message #316420 is a reply to message #316414] Fri, 25 April 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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]

Report message to a moderator

Re: Outer join with variable [message #316451 is a reply to message #316421] Fri, 25 April 2008 03:01 Go to previous messageGo to next message
ekteh27
Messages: 10
Registered: February 2008
Location: Malaysia
Junior Member
Hi Michel,
I agree with your points. Smile Base on few questions you ask in the reply, I feel that I may consider half expert,

How many are in this case and don't know bulk operations?
This I know and i used before.

How many write their PL/SQL like we wrote it in version 7?
I had upgrade myself to use latest syntax/function.

How many use "when others then dbms_output..." or the like?
I never use this before

How many commit/rollback inside a procedure?
A procedure should only have 1 commit at last of program or better don't use commit in procedure.

How many use "execute immediate" to just make a static DML?
Cursor should be better choice for static DML

How many commit after a DDL?
DDL had commit internally, why need commit after DDL?

How many wrongly use triggers and/or get mutating error?
I do this mistake 4 years ago, that's my first and last time to get mutating error. Smile

Next time I'll be more careful when i post any topic in forum. Thanks anyway. Smile

Regards,
EK
Re: Outer join with variable [message #316454 is a reply to message #316421] Fri, 25 April 2008 03:04 Go to previous message
ekteh27
Messages: 10
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
Previous Topic: How to sort this?
Next Topic: pad/fill blank spaces (merged 4 topics)
Goto Forum:
  


Current Time: Sat Dec 10 22:42:23 CST 2016

Total time taken to generate the page: 0.09321 seconds