Code:
SQL>
SQL> @testcase
SQL> --
SQL> column ref_code format a10
SQL> column type format a5
SQL> column text_line format a55
SQL> column key1 format a6
SQL> column key2 format a6
SQL>
SQL> drop table mpr_text;
Table dropped.
SQL> CREATE TABLE MPR_TEXT
2 (
3 REF_CODE VARCHAR2(30 CHAR) NOT NULL,
4 TYPE VARCHAR2(30 CHAR) NOT NULL,
5 KEY1 VARCHAR2(30 CHAR) NOT NULL,
6 KEY2 VARCHAR2(30 CHAR) NOT NULL,
7 LINE_NO NUMBER(10) NOT NULL,
8 TEXT_LINE VARCHAR2(2000 CHAR),
9 CRE_USR VARCHAR2(30 CHAR) NOT NULL,
10 CRE_DAT DATE NOT NULL,
11 MOD_USR VARCHAR2(30 CHAR) NOT NULL,
12 MOD_DAT DATE NOT NULL
13 );
Table created.
SQL>
SQL> --
SQL> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
5 ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', to_date('1/1/2010','mm/dd/yyyy'), 'ME', to_date('2/1/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', to_date('1/15/2010','mm/dd/yyyy'), 'ME', to_date('2/15/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
5 ANOTHER LINE 1 TESTREMARK 2', 'ME', to_date('2/1/2010','mm/dd/yyyy'), 'ME', to_date('3/1/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
5 ANOTHER LINE 2 TESTREMARK 2', 'ME', to_date('2/15/2010','mm/dd/yyyy'), 'ME', to_date('3/15/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', to_date('3/1/2010','mm/dd/yyyy'), 'ME', to_date('4/1/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- fetch data
SQL> select * from mpr_text;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE CRE_US CRE_DAT MOD_US MOD_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ------ --------- ------ ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 ME 01-JAN-10 ME 01-FEB-10
ANOTHER TESTREMARK ON HEADER LINE 2
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE ME 15-JAN-10 ME 15-FEB-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 ME 01-FEB-10 ME 01-MAR-10
ANOTHER LINE 1 TESTREMARK 2
O OP 123456 2 1 LINE 2 TESTREMARK 1 ME 15-FEB-10 ME 15-MAR-10
ANOTHER LINE 2 TESTREMARK 2
O OP 123456 1 2 LINE 1 TESTREMARK 3 ME 01-MAR-10 ME 01-APR-10
5 rows selected.
SQL>
SQL> -- run query
SQL> select ref_code, type, key1, key2, line_no,
2 substr(text_line,
3 case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
4 case
5 when instr(text_line,chr(10),1,x.iter) > 0
6 then instr(text_line,chr(10),1,x.iter) - case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1) + 1 end
7 else length(text_line)
8 end
9 ) text_line,
10 x.iter num_by_linebreak,
11 row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
12 cre_dat
13 from mpr_text t,
14 ( select level iter
15 from dual
16 connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
17 ) x
18 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
19 order by key1, cre_dat, x.iter;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE NUM_BY_LINEBREAK NUM_BY_KEY1 CRE_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ---------------- ----------- ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 1 1 01-JAN-10
O OP 123456 DFLT 1 ANOTHER TESTREMARK ON HEADER LINE 2 2 2 01-JAN-10
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE 1 3 15-JAN-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 1 4 01-FEB-10
O OP 123456 1 1 ANOTHER LINE 1 TESTREMARK 2 2 5 01-FEB-10
O OP 123456 2 1 LINE 2 TESTREMARK 1 1 6 15-FEB-10
O OP 123456 2 1 ANOTHER LINE 2 TESTREMARK 2 2 7 15-FEB-10
O OP 123456 1 2 LINE 1 TESTREMARK 3 1 8 01-MAR-10
8 rows selected.
SQL>
SQL> -- now add a few more rows with a different value of KEY1 to see if this query actually works
SQL> -- for more than one KEY1 value
SQL> insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 values
4 ('O', 'OP', '789012', '1', 1, 'remark line no 1 of key1=789012, key2=1, line_no=1
5 remark line no 2 of key1=789012, key2=1, line_no=1', 'ME', to_date('1/20/2010','mm/dd/yyyy'), 'ME', to_date('1/31/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 values
4 ('O', 'OP', '789012', '1', 2, 'remark line no 1 of key1=789012, key2=1, line_no=2
5 remark line no 2 of key1=789012, key2=1, line_no=2
6 remark line no 3 of key1=789012, key2=1, line_no=2
7 remark line no 4 of key1=789012, key2=1, line_no=2', 'ME', to_date('3/20/2010','mm/dd/yyyy'), 'ME', to_date('3/31/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 values
4 ('O', 'OP', '789012', '2', 1, 'remark line no 1 of key1=789012, key2=2, line_no=1
5 remark line no 2 of key1=789012, key2=2, line_no=1
6 remark line no 3 of key1=789012, key2=2, line_no=1', 'ME', to_date('4/20/2010','mm/dd/yyyy'), 'ME', to_date('4/30/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 values
4 ('O', 'OP', '789012', '2', 2, 'remark line no 1 of key1=789012, key2=2, line_no=2', 'ME', to_date('4/25/2010','mm/dd/yyyy'), 'ME', to_date('5/1/2010','mm/dd/yyyy'));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- check the data in the table
SQL> select * from mpr_text order by key1, cre_dat;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE CRE_US CRE_DAT MOD_US MOD_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ------ --------- ------ ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 ME 01-JAN-10 ME 01-FEB-10
ANOTHER TESTREMARK ON HEADER LINE 2
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE ME 15-JAN-10 ME 15-FEB-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 ME 01-FEB-10 ME 01-MAR-10
ANOTHER LINE 1 TESTREMARK 2
O OP 123456 2 1 LINE 2 TESTREMARK 1 ME 15-FEB-10 ME 15-MAR-10
ANOTHER LINE 2 TESTREMARK 2
O OP 123456 1 2 LINE 1 TESTREMARK 3 ME 01-MAR-10 ME 01-APR-10
O OP 789012 1 1 remark line no 1 of key1=789012, key2=1, line_no=1 ME 20-JAN-10 ME 31-JAN-10
remark line no 2 of key1=789012, key2=1, line_no=1
O OP 789012 1 2 remark line no 1 of key1=789012, key2=1, line_no=2 ME 20-MAR-10 ME 31-MAR-10
remark line no 2 of key1=789012, key2=1, line_no=2
remark line no 3 of key1=789012, key2=1, line_no=2
remark line no 4 of key1=789012, key2=1, line_no=2
O OP 789012 2 1 remark line no 1 of key1=789012, key2=2, line_no=1 ME 20-APR-10 ME 30-APR-10
remark line no 2 of key1=789012, key2=2, line_no=1
remark line no 3 of key1=789012, key2=2, line_no=1
O OP 789012 2 2 remark line no 1 of key1=789012, key2=2, line_no=2 ME 25-APR-10 ME 01-MAY-10
9 rows selected.
SQL>
SQL> -- now run the query again
SQL> select ref_code, type, key1, key2, line_no,
2 substr(text_line,
3 case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
4 case
5 when instr(text_line,chr(10),1,x.iter) > 0
6 then instr(text_line,chr(10),1,x.iter) - case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1) + 1 end
7 else length(text_line)
8 end
9 ) text_line,
10 x.iter num_by_linebreak,
11 row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
12 cre_dat
13 from mpr_text t,
14 ( select level iter
15 from dual
16 connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
17 ) x
18 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
19 order by key1, cre_dat, x.iter;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE NUM_BY_LINEBREAK NUM_BY_KEY1 CRE_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ---------------- ----------- ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 1 1 01-JAN-10
O OP 123456 DFLT 1 ANOTHER TESTREMARK ON HEADER LINE 2 2 2 01-JAN-10
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE 1 3 15-JAN-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 1 4 01-FEB-10
O OP 123456 1 1 ANOTHER LINE 1 TESTREMARK 2 2 5 01-FEB-10
O OP 123456 2 1 LINE 2 TESTREMARK 1 1 6 15-FEB-10
O OP 123456 2 1 ANOTHER LINE 2 TESTREMARK 2 2 7 15-FEB-10
O OP 123456 1 2 LINE 1 TESTREMARK 3 1 8 01-MAR-10
O OP 789012 1 1 remark line no 1 of key1=789012, key2=1, line_no=1 1 1 20-JAN-10
O OP 789012 1 1 remark line no 2 of key1=789012, key2=1, line_no=1 2 2 20-JAN-10
O OP 789012 1 2 remark line no 1 of key1=789012, key2=1, line_no=2 1 3 20-MAR-10
O OP 789012 1 2 remark line no 2 of key1=789012, key2=1, line_no=2 2 4 20-MAR-10
O OP 789012 1 2 remark line no 3 of key1=789012, key2=1, line_no=2 3 5 20-MAR-10
O OP 789012 1 2 remark line no 4 of key1=789012, key2=1, line_no=2 4 6 20-MAR-10
O OP 789012 2 1 remark line no 1 of key1=789012, key2=2, line_no=1 1 7 20-APR-10
O OP 789012 2 1 remark line no 2 of key1=789012, key2=2, line_no=1 2 8 20-APR-10
O OP 789012 2 1 remark line no 3 of key1=789012, key2=2, line_no=1 3 9 20-APR-10
O OP 789012 2 2 remark line no 1 of key1=789012, key2=2, line_no=2 1 10 25-APR-10
18 rows selected.
SQL>
SQL>