Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Combine 3 Select to view

Re: Combine 3 Select to view

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 14 May 2007 17:00:12 -0700
Message-ID: <1179187209.844573@bubbleator.drizzle.com>


Duke wrote:
> I'm trying to get the following 3 statements to produce a view. Each
> statement and sample results are below.
>
> SELECT DEP.DEPARTMENT,
> RC1.REPORT_CODE CORE, RC1.DESCRIPTION CORE_DESC
> FROM
> MSR_DEPARTMENT DEP, MSR_RC_CORE RC1
> WHERE
> DEP.DEPARTMENT = RC1.DEPARTMENT(+)
>
> DEPARTMENT CORE CORE_DESC
> CTS3 A ADS Problem
> CTS3 C Customer Problem
> STX A NO PROBLEM
> STX B GENERAL SOFTWARE SUPPORT
> STX C SITUATES SOFTWARE PROBLEM
> MCS N Martin
> INL
> MCFG
>
> SELECT DEP.DEPARTMENT,
> RC2.REPORT_CODE CAUSE, RC2.DESCRIPTION CAUSE_DESC
> FROM
> MSR_DEPARTMENT DEP, MSR_RC_CAUSE RC2
> WHERE
> DEP.DEPARTMENT = RC2.DEPARTMENT(+);
>
> DEPARTMENT CAUSE CAUSE_DESC
> CTS3 A Mark Test
> STX A SOFTWARE CAUSE
> STX B ROUTING PROBLEM
> MCS R Martin
> INL
> MCFG
>
> SELECT DEP.DEPARTMENT,
> RC3.REPORT_CODE RES, RC3.DESCRIPTION RES_DESC
> FROM
> MSR_DEPARTMENT DEP, MSR_RC_RESOLUTION RC3
> WHERE
> DEP.DEPARTMENT = RC3.DEPARTMENT(+));
>
> DEPARTMENT RES RES_DESC
> CTS3
> STX A NO ACTION TAKEN
> STX B UNDER INVEST
> MCS C Martin - enough said
> INL
> MCFG
>
>
> I want the view to look like:
> DEPARTMENT CORE CORE_DESC CAUSE
> CAUSE_DESC RES RES_DESC
> CTS3 A ADS
> Problem A Mark Test
> CTS3 C Customer Problem
> STX A NO
> PROBLEM A SOFTWARE CAUSE
> A NO ACTION TAKEN
> STX B GENERAL SOFTWARE SUPPORT B
> ROUTING PROBLEM B UNDER INVEST
> STX C SITATEX SOFTWARE PROBLEM
> MCS N
> Martin R
> Martin C Martin - enough said
> INL
> MCFG
>
> Instinctively, I tried the following query, but it produces way too
> many rows because it "duplicates" values.
>
> SELECT DEP.DEPARTMENT,
> RC1.REPORT_CODE CORE, RC1.DESCRIPTION CORE_DESC,
> RC2.REPORT_CODE CAUSE, RC2.DESCRIPTION CAUSE_DESC,
> RC3.REPORT_CODE RESOLUTION, RC3.DESCRIPTION RESOLUTION_DESC
> FROM
> MSR_DEPARTMENT DEP, MSR_RC_CORE RC1, MSR_RC_CAUSE RC2,
> MSR_RC_RESOLUTION RC3
> WHERE
> DEP.DEPARTMENT = RC1.DEPARTMENT(+) AND RC1.DEPARTMENT =
> RC2.DEPARTMENT(+) AND RC2.DEPARTMENT = RC3.DEPARTMENT(+)
> ORDER BY DEPARTMENT;
>
>
> I'm still working on this and would appreciate any help. If you need
> more info, I can send you a short script to create the tables.
>
> THANKS!
>
> Mark.

No version information.
No error message.
No help.

That said ... likely the problem is the multiple outer joins. You should look at using in-line views or ANSI syntax.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon May 14 2007 - 19:00:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US