Re: Problem with simple histogram query

From: Knut Hovda <knut.hovda_at_pti.no>
Date: Wed, 13 Aug 2003 08:48:18 GMT
Message-ID: <mXm_a.1495$dbb.185177600@news.telia.no>

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
> >
> >

> Received on Wed Aug 13 2003 - 03:48:18 CDT