Login or Register to Ask a Question and Join Our Community


Programming

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL for table with column (varchar2 2000) and line break in it
# 8  
Old 05-07-2010
Quote:
Originally Posted by spidermike
That does the trick! But now the iteration for num is not working anymore, and shows just 1 for all lines?!
Ummm...it doesn't in my output as you can see above...did you change the query?
# 9  
Old 05-07-2010
not really ... only replaced your table name with my table name and changed fieldnames accordingly, nothing in the logic Smilie
# 10  
Old 05-10-2010
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  
Old 05-12-2010
create table:

Code:
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):

Code:
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):

Code:
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?!?!

Code:
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

Last edited by spidermike; 05-12-2010 at 05:30 PM..
# 12  
Old 05-13-2010
Quote:
...
And the result I get ... it is also only 5 lines, but it should be more?!?!
...
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 -

Quote:
Originally Posted by spidermike
...
My SQL (actually yours):

Code:
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;

...
should be
Code:
 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 -

Quote:
...
But now the iteration for num is not working anymore, and shows just 1 for all lines?!
...
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 -

Code:
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 !
Last edited by durden_tyler; 05-13-2010 at 11:03 AM..
# 13  
Old 05-14-2010
Quote:
Originally Posted by durden_tyler
@DreamWarrior - Thanks a lot for fixing the bug in the query. Appreciate it !
Hey no problem, I should be thanking you for introducing me to this interesting method of solving the problem.
# 14  
Old 05-14-2010
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Break a line content and print as column

Hi, I have urls in my input file like this (1 Reply)
Discussion started by: tmonk1
1 Replies

2. Shell Programming and Scripting

Split column data if the table has n number of column's with some record

Split column data if the table has n number of column's with some record then how to split n number of colmn's line by line with records Table --------- Col1 col2 col3 col4 ....................col20 1 2 3 4 .................... 20 a b c d .................... v ... (11 Replies)
Discussion started by: Priti2277
11 Replies

3. Shell Programming and Scripting

Split column data if the table has n number of column's

please write a shell script Table -------------------------- 1 2 3 a b c 3 4 5 c d e 7 8 9 f g h Output should be like this --------------- 1 2 3 3 4 5 7 8 9 a b c c d e f g h (1 Reply)
Discussion started by: Priti2277
1 Replies

4. Shell Programming and Scripting

Break a line content and print as column

Hi, I have urls in my input file like this http://unix.com/abc/def http://unix.com/kil/min I want to use the / as separator and print the last content as another column like this http://unix.com/abc/def def http://unix.com/kil/min min I was using awk -F option and then joining the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

5. Shell Programming and Scripting

BASH: Break line, read, break again, read again...

...when the lines use both a colon and commas to separate the parts you want read as information. The first version of this script used cut and other non-Bash-builtins, frequently, which made it nice and zippy with little more than average processor load in GNOME Terminal but, predictably, slow... (2 Replies)
Discussion started by: SilversleevesX
2 Replies

6. Shell Programming and Scripting

the easiest way to break down this column?

i have a one column txt file, which has a large amount of data which look like this a 11 3 b 45 77 r 7 9 blah blah blah what i am trying to do here is for every 3 lines, i want to move the 2nd and 3rd line to the first line, so it will look like this a 11 3 b 45 77 r 7 9 (8 Replies)
Discussion started by: fedora
8 Replies

7. AIX

problem using VARCHAR2

Hi... i have currently installed db2 version 9.1.2 on my AIX machine... No matter what i do,.... VARCHAR2 just doesn't seem to be working! This is the error i ketp getting after i ran a simple create statement... $db2 "create table tst (name VARCHAR2(30));" DB21034E The command was... (2 Replies)
Discussion started by: VGR
2 Replies

8. UNIX for Dummies Questions & Answers

Extracting column names from a table.. SQL with UNIX

:rolleyes: hi there everybody, i need help,... thanks anyway! i am working on a very huge table with the name table1. the problem is that i know only one field name in this table..., working with a ksh environment i don't know how to view the table to check out the field names :confused:. ... (4 Replies)
Discussion started by: fmina
4 Replies

9. Shell Programming and Scripting

how to break mysql dump sql file

Hi folks I have mysql dump which having insert queries, i want to break that file when 10 complete "INSERTS" lines so extract that line and store in 1.sql and 2.sql and for next 10 insert lines. pls guide me how can i do that. Regards, Bash (2 Replies)
Discussion started by: learnbash
2 Replies

10. UNIX for Dummies Questions & Answers

Installing Ms Sql Server 2000 On Unix

Hello As per requirements, can i Install a MSSQL SERVER on UNIX , is it possible., can i install or not.., could u plz give me reply with some installation details.......... Regards&Thanking You sreedhar G (1 Reply)
Discussion started by: sreedhargunda
1 Replies
Login or Register to Ask a Question