Duke wrote:
> 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 does. You can't use ANSI syntax in 8i and earlier releases.
>> No error message.
> Correct
Then no help.
>> No help.
> That's what I'm asking for.
Well if you can't post an example with the problem you are having
how do you expect us to help you? Write your code for you?
>> 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 already answered that ... ANSI syntax or using in-line views.
> I have no idea about in-line views or ANSI syntax.
You will find demos of all of these matters at www.psoug.org in
Morgan's Library. Look up "joins" and "inline views."
Also go to your DBA and ask for help. Additionally go to amazon.com or
your local bookstore and purchase all of Tom Kyte's books.
--
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 Tue May 15 2007 - 10:38:32 CDT
