Re: Stuck on analytics

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@xxxxxxxxx>:

> 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

Other related posts: