Re: Stuck on analytics
- From: Anthony Wilson <amwilson@xxxxxxxxxxxx>
- To: Bill Ferguson <wbfergus@xxxxxxxxx>
- Date: Tue, 20 Feb 2007 12:30:14 +0900
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
