SQL for table with column (varchar2 2000) and line break in it

Applications Programming
1

Hi,

I need a sql statement for a table, which simply stores a text. It has a column ID, key1, key2, ..., text, date etc. The text can be entered using a line break (return) in an oracle form.

ID  key1   key2   text                                                          date
1   K1      K2    any text a user entered with or without line break in it    01/01/2010
...

Assuming there are line breaks in the text column, the statement should show the result as follows:

 
1 K1 K2 any text a user entered    01/01/2010
2 K1 K2 with or without line break 01/01/2010
3 K1 K2 in it                      01/01/2010
...

Any ideas? Thanks in advance!

2

Something like this perhaps ?

SQL> 
SQL> 
SQL> -- check what the data in the table looks like
SQL> select * from t;

    ID KEY1 KEY2 TXT                      DT
---------- ---- ---- ---------------------------------------- ---------
    10 K1    K2   any text a user entered              01-JAN-10
             with or without line break
             in it
    20 J1    J2   just one line here               02-JAN-10

2 rows selected.

SQL> 
SQL> -- now run the SELECT statement so as to spread out the multi-line TXT column into as many rows
SQL>
SQL> --
SQL> select id,
  2          key1,
  3          key2,
  4          substr(txt,
  5             case x.iter when 1 then 1 else instr(txt,chr(10),1,x.iter-1)+1 end,
  6             case
  7               when instr(txt,chr(10),1,x.iter) > 0
  8               then instr(txt,chr(10),1,x.iter) - case x.iter when 1 then 1 else instr(txt,chr(10),1,x.iter-1) end
  9               else length(txt)
 10             end
 11            ) txt,
 12          x.iter num,
 13          dt
 14    from t,
 15          (select level iter
 16             from dual
 17           connect by level <= (select max(length(txt)-length(replace(txt,chr(10))))+1 from t)
 18          ) x
 19   where (x.iter = 1 or instr(txt,chr(10),1,1) > 0)
 20   order by id, x.iter;

    ID KEY1 KEY2 TXT                         NUM DT
---------- ---- ---- ---------------------------------------- ---------- ---------
    10 K1    K2   any text a user entered                   1 01-JAN-10
    10 K1    K2   with or without line break                2 01-JAN-10
    10 K1    K2   in it                           3 01-JAN-10
    20 J1    J2   just one line here                    1 02-JAN-10

4 rows selected.

SQL> 
SQL>

tyler_durden

3

Very nice SQL! Looks good, except for the fakt, that it splits an entry in line 1 and line 2 and there is also a line 3 and 4 with the full line? Like

1    10 K1    K2   any text a user entered                                       01-JAN-10
2    10 K1    K2   with or without line break                                    01-JAN-10
3    10 K1    K2   in it                                                         01-JAN-10
4    10 K1    K2   any text a user enteredwith or without line breakin it        01-JAN-10
5    10 K1    K2   any text a user enteredwith or without line breakin it        01-JAN-10
4

Nope, that's not how it works !
Have a look at the entire post carefully. I have pasted the data in your table before the query is run, and the kind of data the query returns.

So, if you have this in your table -

ID         KEY1 KEY2 TXT                        DT
---------- ---- ---- -------------------------- ---------
        10 K1   K2   any text a user entered    01-JAN-10
                     with or without line break
                     in it
        20 J1   J2   just one line here         02-JAN-10

2 rows selected.

then the query will work on that data and return this -

ID         KEY1 KEY2 TXT                         NUM  DT
---------- ---- ---- --------------------------- ---- ---------
        10 K1   K2   any text a user entered        1 01-JAN-10
        10 K1   K2   with or without line break     2 01-JAN-10
        10 K1   K2   in it                          3 01-JAN-10
        20 J1   J2   just one line here             1 02-JAN-10

4 rows selected.

Note:
(1) Table data has 2 rows only. The query output has 4 rows.

(2) TXT value for ID=10 spanned 3 lines in the table. TXT value is split up and displayed as 3 different rows in query output.

(3) Values of ID, KEY1, KEY2 were not repeated in table data. Because ID=10 is for only one row. But ID=10 and KEY1, KEY2 values are repeated in the query output. ID = 10 is for 3 rows in query output, unlike table data where it is true for only one row.

(4) TXT value for ID=20 spanned only one row in the table. TXT value for ID=20 spans only one row in the query output. In general, if TXT value for a particular ID spans only one row i.e. if it does not have any embedded newline character, then it will be displayed as it is.

HTH,
tyler_durden

5

Ok, lets check in detail what I have ... my table text can be filled from different areas in an application ... there is a header dable and a detail table, and in the app a user is able to enter notes (texttable) on both levels - header and detail. Header ID is 6832698 and if the note is entered on header level, key2 is DFLT. The line_no is the line number on text. If the note has been entered on detail level, key2 is the detail ID, eg. detail record 1 would be key2 = 1, etc.

Table

REF    TYPE  KEY1    KEY2 LINE_NO TEXT_LINE                                                         DT                 
ORD    OP    6832698 DFLT 1       MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 07.05.2010 11:48:54
ORD    OP    6832698 DFLT 2       AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE                   07.05.2010 11:48:54
ORD    OP    6832698 1    1       DETAIL LINE 1 TESTREMARK 1                                        07.05.2010 11:49:41
ORD    OP    6832698 2    1       DETAIL LINE 2 TESTREMARK 1                                        07.05.2010 11:52:56
ORD    OP    6832698 1    2       DETAIL LINE 1 TESTREMARK 3                                        07.05.2010 11:59:50

My select

select ref,
type,
key1,
key2,
line_no,
substr(text_line,
case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
case
when instr(text_line,chr(10),1,x.iter)>0
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) end
else length(text_line)
end
) text_line,
x.iter num,
cre_dat
from text t,
(selectlevel iter
from dual
connect by level<=(select max(length(text_line)-length(replace(text_line,chr(10))))+ 1 from text table)
) x
where(x.iter =1 or instr(text_line,chr(10),1,1)>0)
order by key1, x.iter;

returns

REF   TYPE KEY1    KEY2 LINE_NO TEXT_LINE                                                         NUM DT           
ORD   OP   6832698 DFLT 1       MY TESTREMARK ON HEADER LINE 1                                    1   07.05.2010 11:48:54
ORD   OP   6832698 DFLT 2       AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE                   1   07.05.2010 11:48:54
ORD   OP   6832698 1    1       DETAIL LINE 1 TESTREMARK 1                                        1   07.05.2010 11:49:41
ORD   OP   6832698 2    1       DETAIL LINE 2 TESTREMARK 1                                        1   07.05.2010 11:52:56
ORD   OP   6832698 1    2       DETAIL LINE 1 TESTREMARK 3                                        1   07.05.2010 11:59:50
ORD   OP   6832698 DFLT 1       ANOTHER TESTREMARK ON HEADER LINE 2                               2   07.05.2010 11:48:54
ORD   OP   6832698 DFLT 1       MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 3   07.05.2010 11:48:54
ORD   OP   6832698 DFLT 1       MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 4   07.05.2010 11:48:54

Hope that explains the difference in output.

6

Tyler, awesome query...but I think it had a few flaws as the OP mentioned.

1) It does indeed select more rows than necessary for some lines. Specifically, for every row having more than 1 and fewer line breaks than the maximum number of line breaks found, extraneous lines will be printed. By modifying the WHERE clause this is easily fixed.

2) It includes the line breaks in some cases and others not. Specifically, every row except the first and last would include the line break. I saw this pretty quickly because I made a change to use '+' as the separating character instead of the line break. And, running your query verbatim, I saw a lot of '+' in the output that shouldn't be there.

So, here what I have:

Table:

SQL> desc tst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEY                                                NUMBER(10)
 TXT                                                VARCHAR2(200)

Data in table:

SQL> SELECT key, SUBSTR(txt,1,40) txt FROM tst;

       KEY TXT
---------- ----------------------------------------
         1 This is one line
         2 [2]1of2+[2]2of2
         3 [3]1of4+[3]2of4+[3]3of4+[3]4of4
         4 [4]1of3+[4]2of3+[4]3of3
         5 Another one liner

Here is your query; modified slightly for format and so it'd work with my table (change line break to '+'):

SELECT
	key,
	SUBSTR(txt,
	       CASE x.iter
			WHEN 1 THEN 1
			ELSE INSTR(txt,'+',1,x.iter-1)+1
	       END,
	       CASE
			WHEN INSTR(txt,'+',1,x.iter) > 0 THEN
				INSTR(txt,'+',1,x.iter) -
					CASE x.iter
						WHEN 1 THEN 1
						ELSE INSTR(txt,'+',1,x.iter-1)
					END
			ELSE LENGTH(txt)
	       END
	) txt,
	x.iter num
FROM tst,
	(SELECT LEVEL iter FROM dual
	 CONNECT BY LEVEL <= (SELECT MAX(LENGTH(txt)-LENGTH(REPLACE(txt,'+')))+1 FROM tst)
	) x
WHERE (x.iter = 1 OR INSTR(txt,'+',1,1) > 0)
ORDER BY key, x.iter;

And its output from my data:

       KEY TXT                                             NUM
---------- ---------------------------------------- ----------
         1 This is one line                                  1
         2 [2]1of2                                           1
         2 [2]2of2                                           2
         2 [2]1of2+[2]2of2                                   3
         2 [2]1of2+[2]2of2                                   4
         3 [3]1of4                                           1
         3 [3]2of4+                                          2
         3 [3]3of4+                                          3
         3 [3]4of4                                           4
         4 [4]1of3                                           1
         4 [4]2of3+                                          2
         4 [4]3of3                                           3
         4 [4]1of3+[4]2of3+[4]3of3                           4
         5 Another one liner                                 1

14 rows selected.

You can see the flaws as pointed out about. Here is your query, with changes in bold+red to fix it:

SELECT
	key,
	SUBSTR(txt,
	       CASE x.iter
			WHEN 1 THEN 1
			ELSE INSTR(txt,'+',1,x.iter-1)+1
	       END,
	       CASE
			WHEN INSTR(txt,'+',1,x.iter) > 0 THEN
				INSTR(txt,'+',1,x.iter) -
					CASE x.iter
						WHEN 1 THEN 1
						ELSE INSTR(txt,'+',1,x.iter-1) + 1
					END
			ELSE LENGTH(txt)
	       END
	) txt,
	x.iter num
FROM tst,
	(SELECT LEVEL iter FROM dual
	 CONNECT BY LEVEL <= (SELECT MAX(LENGTH(txt)-LENGTH(REPLACE(txt,'+')))+1 FROM tst)
	) x
WHERE (x.iter <= LENGTH(txt)-LENGTH(REPLACE(txt,'+'))+1)
ORDER BY key, x.iter;

And its output, which is what I believe the OP wants:

       KEY TXT                                             NUM
---------- ---------------------------------------- ----------
         1 This is one line                                  1
         2 [2]1of2                                           1
         2 [2]2of2                                           2
         3 [3]1of4                                           1
         3 [3]2of4                                           2
         3 [3]3of4                                           3
         3 [3]4of4                                           4
         4 [4]1of3                                           1
         4 [4]2of3                                           2
         4 [4]3of3                                           3
         5 Another one liner                                 1

11 rows selected.

Simple fixes, but I figured I'd play around and see if I could get it working. Awesome job, by the way...I wouldn't have thought of that.

7

That does the trick! But now the iteration for num is not working anymore, and shows just 1 for all lines?!

8

Ummm...it doesn't in my output as you can see above...did you change the query?

9

not really ... only replaced your table name with my table name and changed fieldnames accordingly, nothing in the logic :confused:

10

I don't know then...maybe you can post the create script for your table, a few insert statements to put in the offending data, and the SQL you're running.

11

create table:

CREATE TABLE MPR_TEXT
(
 REF_CODE       VARCHAR2(30 CHAR)              NOT NULL,
 TYPE           VARCHAR2(30 CHAR)              NOT NULL,
 KEY1           VARCHAR2(30 CHAR)              NOT NULL,
 KEY2           VARCHAR2(30 CHAR)              NOT NULL,
 LINE_NO        NUMBER(10)                     NOT NULL,
 TEXT_LINE      VARCHAR2(2000 CHAR),
 CRE_USR        VARCHAR2(30 CHAR)              NOT NULL,
 CRE_DAT        DATE                           NOT NULL,
 MOD_USR        VARCHAR2(30 CHAR)              NOT NULL,
 MOD_DAT        DATE                           NOT NULL
)
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
           INITIAL          16000K
           MINEXTENTS       1
           MAXEXTENTS       UNLIMITED
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

insert statement (generated by SQL tool, hope line breaks are in):

Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
ANOTHER LINE 1 TESTREMARK 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
ANOTHER LINE 2 TESTREMARK 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', sysdate, 'ME', sysdate);

My SQL (actually yours):

select ref_code,
          type,
           key1,
           key2,
           line_no,
           substr(text_line,
              case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
              case
                when instr(text_line,chr(10),1,x.iter) > 0
                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
                else length(text_line)
             end
            ) text_line,
          x.iter num,
          cre_dat
    from mpr_text t,
          (select level iter
             from dual
           connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
          ) x
   WHERE (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,'+'))+1)
   order by key1, cre_dat, x.iter;

And the result I get ... it is also only 5 lines, but it should be more?!?!

REF_CODE      TYPE  KEY1    KEY2   LINE_NO  TEXT_LINE                                                           NUM CRE_DAT
O             OP    123456  DFLT   1        MY TESTREMARK ON HEADER LINE 1                                      1     12.05.2010 16:10:17
O             OP    123456  DFLT   2        AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE                     1     12.05.2010 16:10:17
O             OP    123456  1      2        LINE 1 TESTREMARK 3                                                 1     12.05.2010 16:10:17
O             OP    123456  2      1        LINE 2 TESTREMARK 1                                                 1     12.05.2010 16:10:17
O             OP    123456  1      1        LINE 1 TESTREMARK 1                                                 1     12.05.2010 16:10:17
12

Yes, it should be 8 lines, since that's the number of rows spanned by the data in TEXT_LINE column in the table as a whole.

Firstly, the text in bold, red -

should be

 CHR(10)

You find out the length of the text minus the newlines (chr(10)) at that step.

Secondly, I executed your testcase with hard-coded values of "CRE_DAT" and "MOD_DAT" - spreading them out over a larger timespan. This is how your table will fill up in a real-life scenario.

This will also solve your other problem that you posted earlier -

You'd want to include "CRE_DAT" in the ORDER BY clause in order to maintain the continuity of the "TEXT_LINE" values, and also because it is the unique identifier for a "TEXT_LINE".

If you think CRE_DAT may not be unique (too many users inserting data into this table within a second, for instance) then you will need a surrogate key populated via a database sequence and use that in your ORDER BY clause.

Here's my testcase and query -

test@ORA11G>
test@ORA11G> --
test@ORA11G> drop table mpr_text;
Table dropped.
test@ORA11G> 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  )
 14  ;
Table created.
test@ORA11G>
test@ORA11G> --
test@ORA11G> 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.
test@ORA11G> 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.
test@ORA11G> 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.
test@ORA11G> 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.
test@ORA11G> 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.
test@ORA11G> commit;
Commit complete.
test@ORA11G>
test@ORA11G> -- fetch data
test@ORA11G> select * from mpr_text;
REF_CODE   TYPE  KEY1     KEY2        LINE_NO TEXT_LINE                                       CRE_USR  CRE_DAT   MOD_USR  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.
test@ORA11G>
test@ORA11G> -- run query
test@ORA11G> select ref_code,
  2            type,
  3             key1,
  4             key2,
  5             line_no,
  6             substr(text_line,
  7                case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
  8                case
  9                  when instr(text_line,chr(10),1,x.iter) > 0
 10                  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
 11                  else length(text_line)
 12               end
 13              ) text_line,
 14            x.iter num,
 15            cre_dat
 16      from mpr_text t,
 17            (select level iter
 18               from dual
 19             connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
 20            ) x
 21     WHERE (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 22     order by key1, cre_dat, line_no, num;
REF_CODE   TYPE  KEY1     KEY2        LINE_NO TEXT_LINE                                              NUM CRE_DAT
---------- ----- -------- -------- ---------- ----------------------------------------------- ---------- ---------
O          OP    123456   DFLT              1 MY TESTREMARK ON HEADER LINE 1                           1 01-JAN-10
O          OP    123456   DFLT              1 ANOTHER TESTREMARK ON HEADER LINE 2                      2 01-JAN-10
O          OP    123456   DFLT              2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE          1 15-JAN-10
O          OP    123456   1                 1 LINE 1 TESTREMARK 1                                      1 01-FEB-10
O          OP    123456   1                 1 ANOTHER LINE 1 TESTREMARK 2                              2 01-FEB-10
O          OP    123456   2                 1 LINE 2 TESTREMARK 1                                      1 15-FEB-10
O          OP    123456   2                 1 ANOTHER LINE 2 TESTREMARK 2                              2 15-FEB-10
O          OP    123456   1                 2 LINE 1 TESTREMARK 3                                      1 01-MAR-10
8 rows selected.
test@ORA11G>
test@ORA11G>

tyler_durden

@DreamWarrior - Thanks a lot for fixing the bug in the query. Appreciate it !

13

Hey no problem, I should be thanking you for introducing me to this interesting method of solving the problem.

14

Ok thanks guys, I think I'm good now ... it works, except for the right numer iteration per line, but this is something I can figure out myself ...

cheers
Mike

---------- Post updated at 04:06 PM ---------- Previous update was at 01:44 PM ----------

btw now I also see the missunderstanding in the iteration ... your's is for numeric iteration per line break, but I'm looking for an iteration num per line with the same key1 ... means that should show in the case shown above
1
2
3
4
5
6
7
8

15

You can generate that iteration number by the use of the ROW_NUMBER() analytic function.

row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,

Here's the modified testcase -

$ 
$ cat testcase.sql
--
column ref_code format a10
column type format a5
column text_line format a55
column key1 format a6
column key2 format a6

drop table mpr_text;
CREATE TABLE MPR_TEXT
(
  REF_CODE       VARCHAR2(30 CHAR)              NOT NULL,
  TYPE           VARCHAR2(30 CHAR)              NOT NULL,
  KEY1           VARCHAR2(30 CHAR)              NOT NULL,
  KEY2           VARCHAR2(30 CHAR)              NOT NULL,
  LINE_NO        NUMBER(10)                     NOT NULL,
  TEXT_LINE      VARCHAR2(2000 CHAR),
  CRE_USR        VARCHAR2(30 CHAR)              NOT NULL,
  CRE_DAT        DATE                           NOT NULL,
  MOD_USR        VARCHAR2(30 CHAR)              NOT NULL,
  MOD_DAT        DATE                           NOT NULL
);

--
Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', to_date('1/1/2010','mm/dd/yyyy'), 'ME', to_date('2/1/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('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'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
ANOTHER LINE 1 TESTREMARK 2', 'ME', to_date('2/1/2010','mm/dd/yyyy'), 'ME', to_date('3/1/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
ANOTHER LINE 2 TESTREMARK 2', 'ME', to_date('2/15/2010','mm/dd/yyyy'), 'ME', to_date('3/15/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('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'));

commit;

-- fetch data
select * from mpr_text;

-- run query
select ref_code, type, key1, key2, line_no,
       substr(text_line,
              case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
              case
                when instr(text_line,chr(10),1,x.iter) > 0
                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
                else length(text_line)
              end
             ) text_line,
       x.iter num_by_linebreak,
       row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
       cre_dat
  from mpr_text t,
       ( select level iter
           from dual
        connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
       ) x
 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 order by key1, cre_dat, x.iter;

-- now add a few more rows with a different value of KEY1 to see if this query actually works
-- for more than one KEY1 value
insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '1', 1, 'remark line no 1 of key1=789012, key2=1, line_no=1
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'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '1', 2, 'remark line no 1 of key1=789012, key2=1, line_no=2
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', 'ME', to_date('3/20/2010','mm/dd/yyyy'), 'ME', to_date('3/31/2010','mm/dd/yyyy'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '2', 1, 'remark line no 1 of key1=789012, key2=2, line_no=1
remark line no 2 of key1=789012, key2=2, line_no=1
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'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('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'));

commit;

-- check the data in the table
select * from mpr_text order by key1, cre_dat;

-- now run the query again
select ref_code, type, key1, key2, line_no,
       substr(text_line,
              case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
              case
                when instr(text_line,chr(10),1,x.iter) > 0
                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
                else length(text_line)
              end
             ) text_line,
       x.iter num_by_linebreak,
       row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
       cre_dat
  from mpr_text t,
       ( select level iter
           from dual
        connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
       ) x
 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 order by key1, cre_dat, x.iter;

$ 
$ 
$

And here's the test run -

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>

HTH,
tyler_durden

16

Perfekt, that's it then!

Thanks again for your help :slight_smile:

cheers
Mike

17

You are welcome. Glad to be of help !

tyler_durden