sql - Oracle analytic function window defined by data -

I have a table that represents the line-by-line dump of data read from a particular text file format . Each row represents a "master" or "description" line, which is represented by the rec_type code. I would like to write a query that has the "Master" line with related extension rows. I have come up with something that works, but it looks a bit hackish and is interested in better way, if any.

  table mdtest (make rec_seq number main key, VARCHAR2 (3 rec_type) null, rick_data varchar2 (100) not tap); INSERT (1, '100', 'Bill Jones') in MDTest Value; INSERT in MDTest value (2, '200', '20080115, 100.25'); Include MDTest value (3, '100', 'John Smith'); INSERT (4, '200', '200 9 70180.95') in MDTest Value; INSERT in MDTest value (5, '200', '20091231,110.35');  

desired results:

  SEQ_EMP EMP_NAME SEQ_DATA EMP_DATA ======= ========== ===== === =============== 1 bill Jones 2 20080ll5,l00k25 3 John Smith 4 2009070l,80k95 3 John Smith 5 20091231,110.35  

perceptions:

• are processed in order to record Rik_sak are
• the first record type " 100 "
• each "< Code> 100 "The record contains 1 or more" 200 "The following records

Note: This is for Oracle 9i, although we need to upgrade This year should be 11 g r1.

Here's what I've done so far:

  SELECT SEQ_emp , SUBSTR (emp_seq_name, 10) emp_name, seq_data, (selected emp_data MAX (case when rec_type = '100' then rec_seq END)) (undetermined Refrigerator and ROW) seq_emp, MAX (case between the current per RE_seq ROWS when rec_type = '100' THEN TO_CHAR (Arar_sak, FM 00000000 ') ||' | Where '|| Rec_data end) (order rec_seq lines between the past and the present line of limitless) Emp_seq_name, rec_seq Seq_data, Rec_data Emp_data) from rec_type, mdtest Rec_type = '200' sequence by seq_data;  

As you can see, I Max Reporting analytic function is used'm starting from the top of the window, which starts from the top of the current line, so relevant "100" Existing "200" records may be recorded; Then in the external query I am bypassing the unnecessary "100" record

To get the emp_name, I used to attach the rec_seq with the data so that the Max function still raises the correct header record; Then in the external query, I cut off rec_seq

I do other analytical functions and are played with syntax including FIRST_VALUE and syntax, but none of these seem to be easy to do. .; The difficulty is that the window is defined by the value of rec_type instead of having some constant offset.