Re: SQL Solution Desired

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 - 01:40:58 CST