Re: Problem with simple histogram query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 13 Aug 2003 12:47:38 GMT
Message-ID: <bhdc19$vlfs9$1@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 - 07:47:38 CDT