Re: Stuck on analytics

Okay, I got it now. Thanks a lot Anthony!

I went ahead and did it via your first suggestion, with the multiple KEEP... FIRST blocks and got the results I needed.

The results themselves are rather meaningless, as this is merely to show a single possible record from the user's search criteria across multiple tables (and hopefully with the most likely search 'hit'), and they were always getting confused with the cartesian output.

Bill Ferguson

On 2/19/07, Anthony Wilson <amwilson_at_iinet.net.au> wrote:
>
> 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
> >
>

-- 
-- Bill Ferguson

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