Duke wrote:
> On May 14, 8:00 pm, DA Morgan <damor
@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@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> No version information.
> 10gR2 - but for this SQL, I'm not sure it matters
It does. You can't use ANSI syntax in 8i and earlier releases.
>> No error message.
Then no help.
>> No help.
> That's what I'm asking for.
I already answered that ... ANSI syntax or using in-line views.
> I have no idea about in-line views or ANSI syntax.
Morgan's Library. Look up "joins" and "inline views."
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
