Re: SQL Solution Desired

From: PMG <pete_g_at_2xtreme.net>
Date: Fri, 26 Feb 1999 08:34:44 GMT
Message-ID: <36D65CA9.39955C32@2xtreme.net>

Hi Matthias,

I am not sure how to do what you suggest (it's pretty late here - I will try tomorrow). Can you elaborate?

Thanks

Matthias Gresz wrote:

> PMG schrieb:
> >
> > I have a problem coming up with the right SQL for the following
> > situation:
> >
> > I have three tables as follows:
> >
> > A). SALES_TERR
> > --------------------
> > Name Null? Type
> > ------------------------------- -------- ----
> > TERR_CODE NOT NULL VARCHAR2(5)
> > TERR_DESCRIPTION VARCHAR2(30)
> > REPORTS_TO VARCHAR2(5)
> > ALIGNMENT_NO NOT NULL NUMBER
> >
> > B). SALES_FORCE
> > ----------------------
> > Name Null? Type
> > ------------------------------- -------- ----
> > FORCE_ID NOT NULL NUMBER
> > FORCE_LNAME VARCHAR2(30)
> > FORCE_FNAME VARCHAR2(20)
> >
> > C) SALES_FORCE_ALIGNMENTS
> > ---------------------------------------
> > Name Null? Type
> > ------------------------------- -------- ----
> > TERR_CODE NOT NULL VARCHAR2(5)
> > ALIGNMENT_NO NOT NULL NUMBER
> > FORCE_ID NOT NULL NUMBER
> >
> > I am trying to create a query which will list each Territory, Rep Name,
> > Region, Region Code, and Region Manager's Name. The following query does
> > what I want, with one exception. It does not show territories where the
> > region manager position is open, i.e., there is no entry in
> > SALES_FORCE_ALIGNMENTS.
> >
> > The problem seems to be centered on trying to do an outer join on tables
> > A and B, and an outer join on tables B and C.
> >
> > Any suggestions?
> >
> > SELECT st.terr_code TERRITORY,
> > sf.force_fname || ' '
> > || sf.force_lname REP,
> > sf.force_title TITLE,
> > st.terr_description TERR_NAME,
> > st.reports_to REGION,
> > st1.terr_description REGION_NAME,
> > sf1.force_fname || ' '
> > || sf1.force_lname RM
> > FROM sales_terr st,
> > sales_force sf,
> > sales_force_alignments sfa,
> > sales_terr st1,
> > sales_force_alignments sfa1,
> > sales_force sf1
> > WHERE st.alignment_no = 1
> > AND st.terr_code = sfa.terr_code(+)
> > AND st.alignment_no = sfa.alignment_no(+)
> > AND sfa.force_id = sf.force_id(+)
> > AND st1.terr_code = st.reports_to
> > AND st.alignment_no = st1.alignment_no(+)
> > AND st1.terr_code = sfa1.terr_code(+)
> > AND sfa1.alignment_no = 1
> > AND sfa1.force_id = sf1.force_id(+)

>

> Hi,

>

> one point didn't chekc more:
> you should take in account the way oracle handles outer joins:
> 1. first outer join
> 2. apply the rest of the where clause ont othe result set from 1.

>

> In your case that means that step 1 produces a row set containing
> records with vacant manager positions but these ae eleminated by step 2
> cause you there select for records that have sfal.alignment_no =1. So
> the only way to solve the prob is to use inline views to preselect the
> rows that will be outer joined.

>

> HTH
> Matthias
> --
> grema_at_t-online.de

>

> Es gibt nichts Neues mehr.
> Alles, was man erfinden kann, ist schon erfunden worden.
> Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899
Received on Fri Feb 26 1999 - 02:34:44 CST