Re: Combine 3 Select to view

On May 14, 8:00 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

> No version information.

10gR2 - but for this SQL, I'm not sure it matters - It would/should be the same in about any version - maybe 10g has a few more options, but I'm 100% sure this can work be done in any supported version. I just don't have the knowledge to do it.

> No error message.

Correct

> No help.

That's what I'm asking for.

>likely the problem is the multiple outer joins.

I'm sure that's the problem. My question is how to get the three separate queries into a view without duplicate info.

I have no idea about in-line views or ANSI syntax.

That said - thanks for your reply!

Mark. Received on Tue May 15 2007 - 07:51:31 CDT