Re: interesting query problem, how to resolve? Analytic functions?

"Jack Silvey" <depifster_at_yahoo.com> wrote in message news:25c7944d.0305271350.3c7dcc26_at_posting.google.com...
> All,
>
> I have a query problem that might lead to an interesting solution.
>
> Assume three tables, Claims, Members, Drugs.
>
> Claims contains medical claims, Members contains Members, Drugs
> contains Drugs.
>
> One member to many claims, one claim to one drug.
>
> What I need to do is write a query that lets me remove an ENTIRE set
> of member records from the resultset if ONE or more claims were for
> certain drugs.
>
> In other words, if a member went to the doctor (had a claim) five
> times, and the doctor prescribed drug x just once, I don't want to see
> ANY of the records for that member.
>
> The current way the query is written is this:
>
> select *
> from claims, members
> where claims.member_id not in
> (select member_id
> from claims
> where drug_gid in
> (select drug_gid
> from drugs
> where drug like 'SOMEDRUG'));
>
> which works, but slowly, since this is a warehouse and it requires
> multiple visits to the large claims fact table, and the member table
> isn't small either.
>
> It occurs to me that I might be able to use an analytic function for
> this - each partition is a set of member, sort by nulls on top, and if
> the first record has null for a given field, exclude the set. Except I
> don't know how to do this.
>
> Any solutions, input, advice, or twenty dollar bills welcome.
>
> Thanks,
>
> Jack
>
> warehouse dba in Texas

Though you wrote a decent description of your problem, however you did not provide:

• What version of oracle
• What do the table descriptions look like
• What is the real query which works? The one you posted is doing a cartesian join and I do not believe that it works.

Anurag Received on Tue May 27 2003 - 21:11:58 CDT