Home » SQL & PL/SQL » SQL & PL/SQL » problem in outer join query
problem in outer join query [message #352269] Mon, 06 October 2008 22:48 Go to next message
meghna_makadia
Messages: 4
Registered: October 2008
Junior Member
my query is as below

SELECT p.proj_name,o.TRADING_NAME,appl.app_ref_nr,catg.catg_name,
sd.Boys_0_To_5_Sought, sd.BOYS_TO_5_GRANTED,sd.GIRLS_TO_5_GRANTED,r.REGION_NAME, n.NOTE_TEXT
FROM gap_seg_project p,
gap_seg_organisation o,gap_seg_application appl,
gap_seg_category catg,gap_seg_region r,
gap_seg_disability_typ dt,gap_seg_stud_disability sd, gap_seg_appl_notes n, gap_seg_rltd_disability d
WHERE up_gap_seg_sla_doc.up_Total_Amount_Granted(appl.APP_REF_NR)>0
AND r.REGION_ID=appl.REGION_ID
AND o.organisation_id=p.organisation_id
AND catg.CATG_ID=appl.CATG_ID
AND p.proj_id=appl.proj_id
AND appl.APP_REF_NR= n.APP_REF_NR
AND appl.APP_REF_NR=d.APP_REF_NR
AND d.DISABILITY_ID=dt.DISABILITY_ID
AND sd.app_ref_nr = appl.app_ref_nr
AND d.DISABILITY_ID =sd.DISABILITY_ID

here i want to do outer join on gap_seg_stud_disability sd table so i can get data even no match found in gap_seg_stud_disability table.

this gives error
ORA-01417: a table may be outer joined to at most one other table

please give any suggestion to solve this.

Re: problem in outer join query [message #352282 is a reply to message #352269] Mon, 06 October 2008 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01417: a table may be outer joined to at most one other table
 *Cause:  a.b (+) = b.b and a.c (+) = c.c is not allowed
 *Action: Check that this is really what you want, then join b and c first
          in a view.


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.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: problem in outer join query [message #352285 is a reply to message #352282] Mon, 06 October 2008 23:31 Go to previous messageGo to next message
meghna_makadia
Messages: 4
Registered: October 2008
Junior Member
I have done thru view below is my query

SELECT p.proj_name,o.TRADING_NAME,appl.app_ref_nr,catg.catg_name,dt.DISABILITY_NAME,
sd.Boys_0_To_5_Sought, sd.BOYS_TO_5_GRANTED,sd.GIRLS_TO_5_GRANTED,r.REGION_NAME, n.NOTE_TEXT
FROM gap_seg_project p,
gap_seg_organisation o,gap_seg_application appl,
gap_seg_category catg,gap_seg_region r,
gap_seg_disability_typ dt,gap_seg_stud_disability sd, gap_seg_appl_notes n, gap_seg_rltd_disability d
WHERE up_gap_seg_sla_doc.up_Total_Amount_Granted(appl.APP_REF_NR)>0
AND r.REGION_ID=appl.REGION_ID
AND o.organisation_id=p.organisation_id
AND catg.CATG_ID=appl.CATG_ID
AND p.proj_id=appl.proj_id
AND appl.APP_REF_NR= n.APP_REF_NR (+)
AND appl.APP_REF_NR=d.APP_REF_NR
AND d.DISABILITY_ID=dt.DISABILITY_ID
AND sd.app_ref_nr (+) = appl.app_ref_nr
AND d.DISABILITY_ID in (select d.DISABILITY_ID from gap_seg_rltd_disability d,gap_seg_stud_disability sd where d.DISABILITY_ID= sd.DISABILITY_ID (+) )
AND sd.APP_REF_NR =d.APP_REF_NR
AND appl.APP_REF_NR ='2007/51'

its working fine but its give duplicate records looks like not join properly.
Re: problem in outer join query [message #352286 is a reply to message #352285] Mon, 06 October 2008 23:39 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

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.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel



Please follow the rules.It will help you not us.

Quote:

its working fine but its give duplicate records looks like not join properly.



Why don't you use distinct keyword??

Only you have the idea what you are trying to achieve.
We don't have your table structure and data.How can we check
what is wrong by looking the query.

Post a Test Case.Create Table And Insert Statements.



Regards,
Rajat Ratewal

[Updated on: Mon, 06 October 2008 23:43]

Report message to a moderator

Re: problem in outer join query [message #352291 is a reply to message #352286] Tue, 07 October 2008 00:01 Go to previous messageGo to next message
meghna_makadia
Messages: 4
Registered: October 2008
Junior Member
I used distinct but one of my column's data type is long so its giving error like

ORA-00997: illegal use of LONG datatype

Re: problem in outer join query [message #352297 is a reply to message #352269] Tue, 07 October 2008 00:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Moreover use of distinct may not be the efficient way of doing it

Thumbs Up
Rajuvan.
Re: problem in outer join query [message #352299 is a reply to message #352291] Tue, 07 October 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
rajatratewal wrote on Tue, 07 October 2008 06:39
Only you have the idea what you are trying to achieve.
We don't have your table structure and data.How can we check
what is wrong by looking the query.

Explain this.

Regards
Michel


Re: problem in outer join query [message #352302 is a reply to message #352299] Tue, 07 October 2008 00:45 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

rajatratewal wrote on Tue, 07 October 2008 06:39
Only you have the idea what you are trying to achieve.
We don't have your table structure and data.How can we check
what is wrong by looking the query.


Explain this.

Regards
Michel



The thing is self explainatory.
What needed to be explained Michel??./fa/3314/0/

I asked for a test case and said that only OP knows
his/her data and table structures better.If OP does't provide
a test case of data how would we know what's wrong.

Regards,
Rajat


Re: problem in outer join query [message #352303 is a reply to message #352302] Tue, 07 October 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajatratewal, my message was a reply to OP's last post and not to yours. I just asked OP to follow what you said and explain what he wants to achieve.

Regards
Michel
Re: problem in outer join query [message #352309 is a reply to message #352303] Tue, 07 October 2008 00:55 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry i thought it was for me../fa/1587/0/

Regards,
Rajat Ratewal
Previous Topic: How to update CLOB column having more than 4000 chars
Next Topic: Select where date equal today's date
Goto Forum:
  


Current Time: Sat Dec 10 12:35:08 CST 2016

Total time taken to generate the page: 0.19913 seconds