Re: Combine 3 Select to view

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 17 May 2007 19:01:07 -0700
Message-ID: <1179453664.421023@bubbleator.drizzle.com>

Duke wrote:

> On May 15, 11:38 am, DA Morgan <damor..._at_psoug.org> wrote:
>> 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 atwww.psoug.orgin
>> 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
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> If anyone cares - here is the solution:
> 
> Create VIEW RC_SEARCH AS
> SELECT DEPARTMENT, REPORT_CODE, DESCRIPTION, 'CORE' SOURCE
>  FROM MSR_RC_CORE
> UNION
> SELECT DEPARTMENT, REPORT_CODE, DESCRIPTION, 'CAUSE' SOURCE
>    FROM MSR_RC_CAUSE
> UNION
> SELECT DEPARTMENT, REPORT_CODE, DESCRIPTION, 'RESOLUTION' SOURCE
>  FROM MSR_RC_RESOLUTION
> /
> No join, no ASCII syntax or whatever that is anyway, and actually this
> is much faster and works great.
> 
> Mark

And just throw away the outer joins such as:

"WHERE DEP.DEPARTMENT = RC1.DEPARTMENT(+)" Where did you read that into the spec?

-- 
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 Thu May 17 2007 - 21:01:07 CDT