outer join query problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: outer join query problem

  1. #1
    Join Date
    Oct 2008
    Posts
    1

    outer join query problem

    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 syntax gives error
    ORA-01417: a table may be outer joined to at most one other table

    please give any suggestion to solve this.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The problem is that you are joining SD to two tables. Explain this set of predicates first, as it doesn't seem to make any sense:

    Code:
       D.APP_REF_NR       =   APPL.APP_REF_NR      AND
       SD.APP_REF_NR      =   APPL.APP_REF_NR      AND
       SD.DISABILITY_ID   =   D.DISABILITY_ID      AND
    I also noticed that you are not using the GAP_SEG_DISABILITY_TYP table in the resultset, so I'm not sure why it is included.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width