Fine points on analytic functions

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! Received on Wed Oct 27 2004 - 11:26:09 CDT