Home » SQL & PL/SQL » SQL & PL/SQL » outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a result se
outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a result se [message #345191] Tue, 02 September 2008 09:59 Go to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
I've googled, I've read, I've done everything I can think of but I can not figure out how this query does what it does. I am trying to rewrite it in SQL1999 to make it portable. Any help would be greatly appreciated. I just do not understand the (+) operator.


SELECT char_value FROM prefs_mchar a, preferences b
WHERE a.pref_id (+) = b.pref_id;
AND b.pref_id = + PREF_ID
AND iuser_id (+) = 1;



SQL> desc prefs_mchar;
Name Null? Type
----------------------------------------- -------- --------
PREF_ID NOT NULL NUMBER
IUSER_ID NOT NULL NUMBER
SEQ_NUM NOT NULL NUMBER
CHAR_VALUE

NOT NULL VARCHAR2(240)

SQL> desc preferences;
Name Null? Type
----------------------------------------- -------- -------
PREF_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(80)
LOCKABLE NOT NULL NUMBER
DEFAULT_VALUE NOT NULL NUMBER
DESCRIPTION VARCHAR2(240)
CHAR_DEFAULT_VALUE VARCHAR2(180)


many thanks to any and all that take the time to answer this question.

jim s
Re: Please help with a (+) question (newbe) [message #345192 is a reply to message #345191] Tue, 02 September 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

By the way, "AND b.pref_id = + PREF_ID" is not valid.

Regards
Michel
Re: Please help with a (+) question (newbe) [message #345196 is a reply to message #345191] Tue, 02 September 2008 10:17 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You cannot two semi-colon in one sql statement because in sql ";" is a statement terminator. So if you execute that statement it will complain. Read the wonderful manual. Oracle reference manual is too good and it contains so many information. Even after readin the manual come back to us with some specific comments rather than saying I read many documents I cannot understand.

Link for oracle reference manual is http://tahiti.oracle.com

Regards

Raj
Re: Please help with a (+) question (newbe) [message #345201 is a reply to message #345192] Tue, 02 September 2008 10:27 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
that would be a lot to post. but most recently I tried to undestand the query by breaking into smaller parts. I fed this to sqlplus

SELECT char_value FROM prefs_mchar a, preferences b
WHERE a.pref_id (+) = b.pref_id;

and I got 166 null returns

CHAR_VALUE
----------

So trying to duplicate that in ANSI
I tried.

select char_value from prefs_mchar
left outer join preferences on
preferences.pref_id = prefs_mchar.pref_id;

and got
no rows selected

So I figured I was joining with the wrong table and tried

select char_value from prefs_mchar
left outer join preferences on
prefs_mchar.pref_id = preferences.pref_id;

and got no rwos selected

tried
select char_value from prefs_mchar
left outer join prefs_mchar on
preferences.pref_id = prefs_mchar.pref_id;

and got
ERROR at line 3:
ORA-00904: "PREFERENCES"."PREF_ID": invalid identifier

That when I decided I needed help, someone to explain the (+) operator.

FYI the PRED_ID in the orginal query should be a number like 1 or 2 ro 3 etc.

thanks for taking the time to reply

jim s



Re: Please help with a (+) question (newbe) [message #345202 is a reply to message #345196] Tue, 02 September 2008 10:34 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
the semicolin is a typo I made composing my question. If I could find good documentation on the (+) operator I would not be here asking the question. I spend an entire saturday after noon looking for documentation. All I could find was that it (+) is some kind of join. I figured it must be an outer join since it returns nulls.
Re: Please help with a (+) question (newbe) [message #345205 is a reply to message #345201] Tue, 02 September 2008 10:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I cannot believe you cannot find any good documentation in the internet which explains outer join.

http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175
http://en.wikipedia.org/wiki/Join_(SQL)
http://en.wikipedia.org/wiki/Join_(SQL)

Also, one more point to note is
where a = b 

where b = a

Both will lead the same result it doesn't matter how you specify it. You should read the ANSI SQL standard .

Last but not least read this link. I don't think anybody else can give more information than this link.
http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

After reading all these information come back to us with your observation.

Hope this helps.

Regards

Raj

P.S : I got all these information by searching in google.

[Updated on: Tue, 02 September 2008 10:47]

Report message to a moderator

Re: Please help with a (+) question (newbe) [message #345219 is a reply to message #345191] Tue, 02 September 2008 11:24 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
The O'Reily link might help, at least it has one example of using the (+) operator. Thanks. I can find tons of good info on all kinds of different joins. what I can not find is an explaination of the (+) operator. I am only guessing that it is a outer join.
I have no idea if it is a left outer join, a right outer join or a natural outer join. I have no idea if it is operating on table a or table b.

this
SELECT char_value FROM prefs_mchar a, preferences b
WHERE a.pref_id (+) = b.pref_id;

is a pretty simple query, prefs_mchar has no values in the table, prefernces has 166. This query returns 166 null records.

So what I need to do is write an ANSI query to do the same thing for every pref_id in prefeneces return the coresponding char_value or did if there isn't one return a null.

I have tried every possible combination of the following

select char_value from prefs_mchar a, preferences b
left outer join preferences on
b.pref_id = a.pref_id;

I've tried
left outer join prefs_mchar on

I've tried swaping the a and b
I've tried right outer join
I've tried outer join

I keep getting
various versions of
ERROR at line 3:
ORA-00904: "A"."PREF_ID": invalid identifier

Which I do not understand since pref_id is the unique key to both tables.

I've tried EVERYTHING, which is why I am asking for help on a very simple query, obviously there is something I am not getting.

thanks

jim s



Re: Please help with a (+) question (newbe) [message #345223 is a reply to message #345219] Tue, 02 September 2008 11:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Did you read the last link I have posted ? It is the perfect link till date I have found on outer join. It compares and explains how it works.

Regards

Raj
Re: Please help with a (+) question (newbe) [message #345224 is a reply to message #345191] Tue, 02 September 2008 11:38 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
Guess this newbe came to the wrong place to ask for help.

thanks anyway

jim s.
how do you constrain a result set after a left outer join [message #345244 is a reply to message #345191] Tue, 02 September 2008 13:29 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
I am trying to port this sql statement

SELECT TO_CHAR(NVL(int_value, default_value)), b.pref_id
FROM prefs_oneint a, preferences b
WHERE a.pref_id (+) = b.pref_id
AND b.pref_id = 81
AND iuser_id (+) = 1;

to Sql1999

I have got this part of the query working

select TO_CHAR(NVL(int_value, default_value))
from preferences
left outer join prefs_oneint
on prefs_oneint.pref_id = preferences.pref_id

the above retrieves the correct result set
however then I add the constrain

AND preferences.pref_id = 81

the result set is not constrained to just pref_id 81.

Does anyone know how I can constrain the results of the left outer join just pref_id=81? (And inner join will not work)
and what am I going to do about the
AND iuser_id (+) = 1;

thanks in advance.

jim s





Re: how do you constrain a result set after a left outer join [message #345248 is a reply to message #345244] Tue, 02 September 2008 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 02 September 2008 17:04

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.


In addition, there is no need to start a new topic.

Regards
Michel

[Updated on: Tue, 02 September 2008 13:45]

Report message to a moderator

Re: how do you constrain a result set after a left outer join [message #345255 is a reply to message #345244] Tue, 02 September 2008 14:06 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Jim,

Can you post some test data? Also follow the posting guidelines which Michel has mentioned about. It will help others to understand the problem you are facing with your query better.

Also show the results of the query you have tried?

And one more thing the links S. Rajaram posted is very informative. Go through it one more time.

Regards,
Jo
Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345269 is a reply to message #345191] Tue, 02 September 2008 14:54 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
I just spend 30 minutes typing up a post and it disappeared on me.

I will try again. This time much shorter.

The first thing I do when joining a forum is read the posting guide. If someone can point out my mistakes I will try not to repeat them. I came here hoping to get help from folks that know SQL far better then I do.

I read Raj links, most of them I read yesterday, but I did get some good info on the (+) operator at the O'Reily site Raj linked to. In fact that got me pass problem one, understanding the (+) operator. Still a little fuzzy but no longer totally lost.

My problem now is this query

SELECT TO_CHAR(NVL(int_value, default_value)), b.pref_id
FROM prefs_oneint a, preferences b
WHERE a.pref_id (+) = b.pref_id
AND b.pref_id = 81
AND iuser_id (+) = 1;

To illustrate my problem better I am going to break the query above into 2 parts. The first part

SELECT TO_CHAR(NVL(int_value, default_value)), b.pref_id
FROM prefs_oneint a, preferences b
WHERE a.pref_id (+) = b.pref_id

Creates a large result set, int_value from table prefs_oneint
and default_value from preferences and I am guessing pref_id as well.

The second part
AND b.pref_id = 81

constrains the result set to only those rows that have pref_id=81.

OK cool, now lets do the same in SQL 1999
get the super set of results

SELECT TO_CHAR(NVL(int_value, default_value))
FROM preferences left outer join prefs_oneint
ON prefs_oneint.pref_id = preferences.pref_id

This part works great. Now constrain results to pref_id=81;

SELECT TO_CHAR(NVL(int_value, default_value))
FROM preferences left outer join prefs_oneint
ON prefs_oneint.pref_id = preferences.pref_id
AND preferences.pref_id = 81

This part does not work, the results are the same as the super set, the AND has no effect.

that is my problem, any help would be great.

jim s

PS I indented the SQL code, but it is not being indented in the preview.











Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345279 is a reply to message #345269] Tue, 02 September 2008 15:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Jim,

Try giving preferences.pref_id = 81 under a where condition.

Your query will be something like the following:
   SELECT TO_CHAR(NVL(int_value, default_value))
     FROM preferences left outer join prefs_oneint
     ON (prefs_oneint.pref_id = preferences.pref_id)
     where preferences.pref_id = 81


I have not tried this query yet. But I guess it will solve your problem. In your original query you are putting up a constraint on the join condition. That wont help you much because you are putting up a constraint on the join condition (I hope I am not confusing you.) If you want to restrict the output of the entire query use the restricting condition in the where clause.

Let me know how it goes.

Regards,
Joice
Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345286 is a reply to message #345279] Tue, 02 September 2008 15:38 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
thanks that worked, I was under the impression that I could not use a where after a join. foolish me. thanks again. Cool
Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345735 is a reply to message #345279] Thu, 04 September 2008 12:06 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
One more question if that is OK, notice in the orginal query there is this at the end

AND iuser_id (+) = 1;

what the last line does is ensure a 1 is selected if the result set is empty. Any idea on how I can do that with my current query

SELECT TO_CHAR(NVL(int_value, default_value))
FROM preferences left outer join prefs_oneint
ON (prefs_oneint.pref_id = preferences.pref_id)
where preferences.pref_id = 81

thanks much.

jim s


Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345742 is a reply to message #345735] Thu, 04 September 2008 12:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

jpsb wrote on Thu, 04 September 2008 22:36
One more question if that is OK, notice in the orginal query there is this at the end

AND iuser_id (+) = 1;

what the last line does is ensure a 1 is selected if the result set is empty. Any idea on how I can do that with my current query



Jim,

I believe you have not been reading the link provided by S. Rajaram.

Information gathered from S.Rajaram's Links in this post

In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5) indicates that this filter must be applied to the table T2 before the outer join takes place.



Try to change your query keeping the above quote in mind.

Let us know how you progress with your query.

Regards,
Jo
Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345752 is a reply to message #345742] Thu, 04 September 2008 13:25 Go to previous messageGo to next message
jpsb
Messages: 10
Registered: September 2008
Junior Member
I'm a pertty good Java programmer and I'm OK with SQL
however I am just now learning about left outer joins
so let me tell you what I know about the query and where I am
having metal dificulities.

the query

......SELECT TO_CHAR(NVL(int_value, default_value))
......FROM prefs_oneint a, preferences b
......WHERE a.pref_id (+) = b.pref_id
......AND b.pref_id = 81
......AND iuser_id (+) = 1;

......SELECT TO_CHAR(NVL(int_value, default_value))
......TO_CHAR convert the result to a varchar
......NVL select the not null value

......FROM prefs_oneint a, preferences b
operate on tables prefs_oneint and preferences

......WHERE a.pref_id (+) = b.pref_id

left outer join on table (using pref_id) prefs_oneint,
select all values from prefs_oneint even if value is null
so the joined result looks like

preferences ............prefs_oneint
pref_id, default_value, pref_id, int_value, iuser_id

......AND b.pref_id = 81
constrain result to pref_id=81

......AND iuser_id (+) = 1;
left outer join on prefs_oneint using iuser_id = 1


hopefully the above is correct.

now my query

......SELECT STR(ISNULL(int_value, default_value))
......FROM preferences left outer join prefs_oneint
......ON (prefs_oneint.pref_id = preferences.pref_id
......AND prefs_oneint.iuser_id = 1)
......WHERE preferences.pref_id = 81

this seemed to work, is it correct?

jim s
Re: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a resul [message #345754 is a reply to message #345752] Thu, 04 September 2008 13:28 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Jim,

Yes. That's correct.

Hope you understood the concept.

Regards,
Jo

Previous Topic: filling in missing data
Next Topic: Truncate execution
Goto Forum:
  


Current Time: Fri Dec 09 17:34:01 CST 2016

Total time taken to generate the page: 0.41185 seconds