Re: Combine 3 Select to view

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 Received on Thu May 17 2007 - 14:37:51 CDT