Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with simple histogram query

Re: Problem with simple histogram query

From: Isaac Blank <izblank_at_yahoo.com>
Date: Wed, 13 Aug 2003 18:27:28 GMT
Message-ID: <kqv_a.1445$Fo4.25300129@newssvr13.news.prodigy.com> 





    Yes, you got it right:  with outer joins, the order of joins is
important, and the right way to enforce it is through inline views (AKA
table expressions).  The equivalent 9i syntax would be this:



select count(sc2.uniformity) as occurrences, (cd.minvalue || ' to ' ||
cd.maxvalue) as interval, cd.description
from ( coeffdescription cd

        INNER JOIN coefftype ct ON
        ct.co_id = cd.coefftypeid
        and ct.type = 'Uniformity' )
    LEFT OUTER JOIN
        (select sc.uniformity
        from scurve sc, sample s
        where s.wellid in (101)
        and s.s_id = sc.sampleid
        and sc.typeid = 1
        and s.depth >= 1100
        and s.depth <= 1200) sc2 ON
        sc2.uniformity >= cd.minvalue
        and sc2.uniformity < cd.maxvalue


group by cd.minvalue, cd.maxvalue, cd.description
order by cd.minvalue;




"Knut Hovda" <knut.hovda_at_pti.no> wrote in message
news:1_q_a.1499$dbb.185756672_at_news.telia.no...


> Hello,

>

> Just for the record, the following query did the trick:

>

> select count(sc2.uniformity) as occurrences, (cd.minvalue || ' to ' ||

> cd.maxvalue) as interval, cd.description

> from coeffdescription cd, coefftype ct,

> (select sc.uniformity

> from scurve sc, sample s

> where s.wellid in (101)

> and s.s_id = sc.sampleid

> and sc.typeid = 1

> and s.depth >= 1100

> and s.depth <= 1200) sc2

> where sc2.uniformity(+) >= cd.minvalue

> and sc2.uniformity(+) < cd.maxvalue

> and ct.co_id = cd.coefftypeid

> and ct.type = 'Uniformity'

> group by cd.minvalue, cd.maxvalue, cd.description

> order by cd.minvalue;

>

> The key was to split the query in a main and a subquery, and use the

result


> from the subquery in the right outer join.

>

> Thanks for your help!

>

> Regards,

>

> Knut

>

>

>

>

> "Knut Hovda" <knut.hovda_at_pti.no> wrote in message

> news:yKq_a.1498$dbb.188066816_at_news.telia.no...

> > Hello,

> >

> > Thanks. We are thinking along the same lines, I just tried the same

thing.


> > Unfortunately, it did not help.

> >

> > I think the general problem is that I need to make a right outer join

> > between coeffdescription and the RESULT of the inner join between sample

> and

> > scurve, not just scurve. I've reduced the query, and it works fine until

I


> > add the join between sample and scurve.

> >

> > Any hints on how to ensure the right outer join relates to the result of

> the

> > inner join? I guess I would need a subquery and use this in the right

> outer

> > join?

> >

> > Regards,

> >

> > Knut

> >

> >

> >

> > "Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message

> > news:bhdc19$vlfs9$1_at_ID-82536.news.uni-berlin.de...

> > >

> > > > Hello,

> > > >

> > > > Thank you very much for your reply, that works fine. (Still a

problem,


> > > > though, see below.) Using the old Oracle syntax (we're developing on

> 9,

> > but

> > > > also testing on 8) also works:

> > > >

> > > > select count(sc.uniformity) as occurrences, cd.description

> > > > from sc2 sc, cd2 cd

> > > > where sc.uniformity(+) >= cd.minvalue

> > > > and sc.uniformity(+) < cd.maxvalue

> > > > group by cd.description;

> > > >

> > > > But I'm still having a problem when extending this simple example to

> our

> > > > "real" query, which involves two more tables with inner joins to the

> two

> > > > tables that have a right outer join.

> > > >

> > > > To illustrate, let me show some partial queries. The one below shows

> the

> > > > dataset we will be counting, having 2 records:

> > > >

> > > > select sc.sampleid, sc.uniformity

> > > > from scurve sc, sample s

> > > > where s.wellid in (101)

> > > > and sc.typeid = 1

> > > > and s.s_id = sc.sampleid

> > > > and s.depth >= 1100

> > > > and s.depth <= 1200;

> > > >

> > > >   SAMPLEID UNIFORMITY


> > > > ---------- ----------

> > > >        181      2,639

> > > >        182      3,533

> > > >

> > > > Next, the categories to which they will be assigned, are:

> > > >

> > > > select (cd.minvalue || ' to ' || cd.maxvalue) as interval,

> > cd.description

> > > > from coeffdescription cd, coefftype ct

> > > > where ct.co_id = cd.coefftypeid

> > > > and ct.type = 'Uniformity'

> > > > order by cd.minvalue;

> > > >

> > > > INTERVAL         DESCRIPTION


> > > > ---------------- ---

> > > > 0 to 3           Uniform sand

> > > > 3 to 5           Intermediate: 3<Uc<5

> > > > 5 to 999999      Non-uniform sand

> > > >

> > > > So, the resulting combined query should have counts 1 for the first

> and

> > > > second category, but 0 for the third:

> > > >

> > > > select count(sc.uniformity) as occurrences, (cd.minvalue || ' to '

||


> > > > cd.maxvalue) as interval, cd.description

> > > > from scurve sc, coeffdescription cd, coefftype ct, sample s

> > > > where s.wellid in (101)

> > > > and sc.uniformity(+) >= cd.minvalue

> > > > and sc.uniformity(+) < cd.maxvalue

> > > > and sc.typeid = 1

> > > > and s.s_id = sc.sampleid

> > > > and ct.co_id = cd.coefftypeid

> > > > and ct.type = 'Uniformity'

> > > > and s.depth >= 1100

> > > > and s.depth <= 1200

> > > > group by cd.minvalue, cd.maxvalue, cd.description

> > > > order by cd.minvalue;

> > > >

> > > > OCCURRENCES INTERVAL   DESCRIPTION


> > > > ----------- ---------- ---

> > > >           1             0 to 3                 Uniform sand

> > > >           1             3 to 5                 Intermediate: 3<Uc<5

> > > >

> > > > The problem is, there is no count for the third category (should

have


> > been

> > > > count 0).

> > > >

> > > > I also tried the newer syntax for the outer join, but with the same

> > result

> > > > (sorry about the mixed old and new syntax):

> > > >

> > > > select count(sc.uniformity) as occurrences, (cd.minvalue || ' to '

||


> > > > cd.maxvalue) as interval, cd.description

> > > > from scurve sc RIGHT OUTER JOIN coeffdescription cd ON

> > > > (sc.uniformity >= cd.minvalue

> > > > and sc.uniformity < cd.maxvalue), coefftype ct, sample s

> > > > where s.wellid in (101)

> > > > and s.s_id = sc.sampleid

> > > > and sc.typeid = 1

> > > > and ct.co_id = cd.coefftypeid

> > > > and ct.type = 'Uniformity'

> > > > and s.depth >= 1100

> > > > and s.depth <= 1200

> > > > group by cd.minvalue, cd.maxvalue, cd.description

> > > > order by cd.minvalue;

> > > >

> > > > From what I can see, the joins must be:

> > > >

> > > > - Inner join between sample and scurve

> > > > - Right outer join between scurve and coeffdescription

> > > > - Inner join between coeffdescription and coefftype

> > > >

> > > > Any hints on what is wrong would be appreciated! (Regarding syntax,

> it's

> > > > sufficient that the solution works on Oracle 9).

> > > >

> > > > Regards,

> > > >

> > > > Knut

> > > >

> > > >

> > > > -----

> > > >

> > > > "Isaac Blank" <izblank_at_yahoo.com> wrote in message

> > > > news:EB9_a.145$Mp1.20900925_at_newssvr21.news.prodigy.com...

> > > >> select count(sc.uniformity) as occurrences, cd.description

> > > >> from sc2 sc RIGHT OUTER JOIN cd2 cd

> > > >> ON sc.uniformity >= cd.minvalue

> > > >> and sc.uniformity < cd.maxvalue

> > > >> group by cd.description

> > > >>

> > > >>

> > > >> "Knut Hovda" <knut.hovda_at_pti.no> wrote in message

> > > >> news:7N6_a.1487$dbb.185756160_at_news.telia.no...

> > > >> > Hello,

> > > >> >

> > > >> > We have a problem with a query to create a histogram (counting

> > > > occurrences

> > > >> > in categories). Consider the following simplified example:

> > > >> >

> > > >> > create table cd2 (minvalue number(16), maxvalue number(16),

> > description

> > > >> > varchar(30));

> > > >> > create table sc2 (uniformity number(16));

> > > >> > insert into cd2 values (0,2,'small');

> > > >> > insert into cd2 values (2,4,'medium');

> > > >> > insert into cd2 values (4,6,'large');

> > > >> > insert into sc2 values (1);

> > > >> > insert into sc2 values (3);

> > > >> >

> > > >> > select count(*) as occurrences, cd.description

> > > >> > from sc2 sc, cd2 cd

> > > >> > where sc.uniformity >= cd.minvalue

> > > >> > and sc.uniformity < cd.maxvalue

> > > >> > group by cd.description;

> > > >> >

> > > >> > gives

> > > >> >

> > > >> > OCCURRENCES DESCRIPTION


> > > >> > ----------- --------------

> > > >> >           1 medium

> > > >> >           1 small

> > > >> >

> > > >> > But note that the 'large' category is not displaying, since no

rows


> > in

> > > > sc2

> > > >> > fall in this category . We want the result to display (sorting

> order

> > is

> > > >> not

> > > >> > relevant):

> > > >> >

> > > >> >           0 large

> > > >> >           1 medium

> > > >> >           1 small

> > > >> >

> > > >> > How can we achieve this? (We first thought this was a simple

matter


> > of

> > > > an

> > > >> > outer join missing, but somehow we seem to miss the trick here.)

> > > >> >

> > > >> > In advance, thanks for your help.

> > > >> >

> > > >> > Regards,

> > > >> >

> > > >> > Knut

> > > >> >

> > > >> >

> > > >>

> > >

> > >

> > > and sc.typeid(+) = 1

> > >              ^^^

> > >

> > > Not testet as I was too lazy trying to understand your tables and the

> > > like.

> > >

> > > Still hope it helps

> > >

> > > Rene

> > >

> > > -- 

> > >   Rene Nyffenegger

> > >   www.adp-gmbh.ch

> >

> >

>

>

>

Received on Wed Aug 13 2003 - 13:27:28 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US