Combine 3 Select to view

From: Duke <mark.schubert_at_gmail.com>
Date: 14 May 2007 13:43:40 -0700
Message-ID: <1179175420.643035.6800@k79g2000hse.googlegroups.com>

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. Received on Mon May 14 2007 - 15:43:40 CDT