Re: Problem with simple histogram query

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 - 08:24:13 CDT