Re: Stuck on analytics

From: Anthony Wilson <amwilson_at_iinet.net.au>
Date: Tue, 20 Feb 2007 12:30:14 +0900
Message-ID: <1171942214.45da6b4680989@mail.iinet.net.au>

Hi Bill,

Without your code I can't tell you how you're ending up with cartesian joins, however I can suggest a couple of approaches to retrieving the other columns from the child tables.

Firstly, you could add a KEEP ... FIRST block for each of the other fields you wish to retrieve:

select dep.dep_id
, min(com.line) keep (

   dense_rank first
   order by com.line asc
) com_line
, min(com.other_field) keep (

   dense_rank first
   order by com.line asc
) com_other_field
...

If you wish to retrieve ALL of the fields from the child tables, the above may seem a bit clunky to you, so you could use the initial query as an inline view as below (this assumes that (dep_id, line) is a unique key in the commodity table, and similarly for the other child tables):

select *
from deposits d

, commodity c
, model_type m
, locations l
, (

   select dep.dep_id
   , min(com.line) keep (

      dense_rank first
      order by com.line asc

   ) com_line
   , min(mod.rec) keep (

      dense_rank first
      order by mod.rec asc

   ) mod_rec
   , min(loc.line) keep (

      dense_rank first
      order by loc.line asc

   ) loc_line
   from deposits dep

   , commodity com
   , model_type mod
   , locations loc

   where com.dep_id = dep.dep_id
   and mod.dep_id = dep.dep_id
   and loc.dep_id = dep.dep_id
   group by dep.dep_id
) v
where d.dep_id = v.dep_id

and d.dep_id = c.dep_id
and d.dep_id = m.dep_id
and d.dep_id = l.dep_id
and c.line = v.com_line
and m.rec = v.mod_rec
and l.line = v.loc_line

/

I haven't tested either of these as I don't have your sample data script here, but hopefully they will give you some ideas. The choice is of course up to you and I guess it will depend on performance in your environment, and your personal preference.

cheers,
Anthony

Quoting Bill Ferguson <wbfergus_at_gmail.com>:

> Hi Anthony,
>
> Thanks for the code. It works great for just the dep table, but when I add
> in the additional fields from any of the other tables I start to get a
> cartesian join again.
>
> I expanded line one (and the GROUP BY) to include the other fields from the
> DEPOSITS table and ran it fine. I then added the fields from the COMMODITY
> table to line one and wound up with a cartesion join. I tried moving the com
> fields to a new line 6, but still wind up with a cartesian join.
>
> How/where do I add the other fields from the other tables without getting a
> cartesian join?
>
> Thanks,
> Bill Ferguson
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 19 2007 - 21:30:14 CST