Re: Fine points on analytic functions

Roger Redford wrote:

> Dear experts,
>
> I have a complex report to create, and I'm wondering if
> analytic functions can help me. I'm a newbie with them,
> and I've been having some success, but I'm not sure if they
> can help me here or not.
>
> There are two tables in a standard 1:M relationship.
> Let's call the main table the master table. The
> many table is let's say, master_history. In master_history,
> we keep a log of all activity with the master table.
>
> The PK of MASTER_HISTORY is the first two fields:
>
>
> MASTER_HISTORY
> --------------
> master_id pk
> master_history_date pk
>
> master_history_code
>
>
>
> master_history_code is a code as to the activity. It
> can have a number of different values. The length
> of the code, 1 or 3, has different meanings too.
>
>
>
> We are trying to produce a report that looks like:
>
> master_id cnt_length_3, cnt_length_1, cnt_code_x, cnt_code_y, (etc) latest_code
>
>
> For cnt_length_3, I tried:
>
> Sum(DECODE (to_char(LENGTH(master_history_code)), '3', 1, 0))
>
> However, this counts many records for the same master_id.
>
> I'd like to get a distinct count or whether there is a 3 digit
> code or not. If I find one, or one hundred, three digit codes,
> count one only. Same for cnt_code_x, cnt_code_y, etc.
>
> Latest code is the most recent activity with that record.
>
>
> Traditionally, I would use inline views for this kind of thing.
> But that requires a separate inline view and a join for each field,
> putting a big load on the system.
>
> Is there a way to use analytic functions to do what I'm hoping?
>
> Thanks a lot!

Two thoughts:

1. This sure looks like homework
2. What version of Oracle? -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)