Re: SQL question on an outer join

On May 11, 8:05 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On May 11, 12:07 am, fergus <fergus_v..._at_yahoo.com> wrote:
>
>
>
>
>
> > On May 10, 11:21 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > Thanks for posting the DDL and DML for the setup.
>
> > > Using the suggestion offered by Pat, with a small modification:
> > > SELECT
> > >   TABLEA.COLA,
> > >   TABLEB.COLA_FK,
> > >   TABLEB.COLC
> > > FROM
> > >   TABLEA
> > > LEFT JOIN
> > >   TABLEB
> > > ON
> > >   TABLEA.COLA=TABLEB.COLA_FK
> > >   AND TABLEB.COLC=99999;
>
> > >       COLA    COLA_FK       COLC
> > > ---------- ---------- ----------
> > >          2          2      99999
> > >          1
>
> > > This is the way I would commonly set up a SQL statement to meet a
> > > similar requirements:
> > > SELECT
> > >   TABLEA.COLA,
> > >   TABLEB.COLA_FK,
> > >   TABLEB.COLC
> > > FROM
> > >   TABLEA,
> > >   TABLEB
> > > WHERE
> > >   TABLEA.COLA=TABLEB.COLA_FK(+)
> > >   AND TABLEB.COLC(+)=99999;
>
> > >       COLA    COLA_FK       COLC
> > > ---------- ---------- ----------
> > >          1
> > >          2          2      99999
>
> > > Will the value of interest always be 99999, or will it be the highest
> > > value with a matching COLA_FK?  If you are looking for the highest
> > > value, please supply the four digit version of Oracle that you are
> > > using (10.2.0.2, 11.1.0.6, 8.1.7.3, etc.).
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
>
> > Aha - that is very cool.  I had figured out the
> >   >   TABLEA.COLA=TABLEB.COLA_FK(+)
>
> > but did not know I could do this:
>
> >  >   AND TABLEB.COLC(+)=99999;
>
> > As a matter of fact you are right - how did you guess - in my cases,
> > the interest of value would be max of whatever
> >  is in TABLEB.COLC - if the row with 99999 does not exist, then the
> > sql returns the fow with data in COLC=12345.
> >  Also, working with ORACLE version 9.2.0.8.
>
> > Thank you once again,
>
> > Regards,
> > Fergus
>
> It looks like the DENSE_RANK analytical function, an inline view, and
> an outer join are required.
>
> First, let's introduce a little more data to make certain that we
> cannot query for a specific value of COLC and return the expected
> results:
> INSERT INTO TABLEA VALUES (3);
> INSERT INTO TABLEB VALUES (13,2,111111);
> INSERT INTO TABLEB VALUES (13,3,11);
>
> Next, we try an experiment with the DENSE_RANK function to separate
> the rows by the value of COLA_FK (caused by the PARTITION BY
> directive) and rank the values sorted from highest to lowest (caused
> by the DESC directive):
> SELECT
>   COLB,
>   COLA_FK,
>   COLC,
>   DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
> FROM
>   TABLEB;
>
>       COLB    COLA_FK       COLC         DR
> ---------- ---------- ---------- ----------
>         13          2     111111          1
>         12          2      99999          2
>         11          2      12345          3
>         13          3         11          1
>
> We are only interested in the rows with DR = 1, so we need a way to
> eliminate the unnecessary rows.  If we slide the above SQL statement
> into an inline view, we are able to add a WHERE clause that restricts
> the results to the rows containing the highest COLC value per COLA_FK
> value.  We can then alias the inline view (as B), and join it to
> TABLEA as before:
> SELECT
>   TABLEA.COLA,
>   B.COLA_FK,
>   B.COLC
> FROM
>   TABLEA,
>   (SELECT
>     COLB,
>     COLA_FK,
>     COLC,
>     DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
>   FROM
>     TABLEB) B
> WHERE
>   TABLEA.COLA=B.COLA_FK(+)
>   AND B.DR(+)=1;
>
>       COLA    COLA_FK       COLC
> ---------- ---------- ----------
>          2          2     111111
>          3          3         11
>          1
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

wow!
Thank you very much - that would be my introduction to analytical functions in oracle :-)
Awesome!

• fergus