Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle 10g - Concatonate Multiple Rows - PART 2

Posted on 2013-05-17
5
Medium Priority
?
563 Views
Last Modified: 2013-05-18
A slight revision to my first request.

I originally asked  to concatonate mutliple rows of text together in a select query. The answer provided was

Removing the space concatenation(which also removes the need for the trim function) would produce results like this -
SQL> select
  2             EXTRACT(XMLAGG(XMLELEMENT("s", line_text) order by line_num asc)
, '/s/text()').getclobval()
  3  from tab1
  4  where CompCode = 'A1'
  5     and Ord_num = 628649
  6  group by compCode,ord_num;

EXTRACT(XMLAGG(XMLELEMENT("S",LINE_TEXT)ORDERBYLINE_NUMASC),'/S/TEXT()').GETCLOB
--------------------------------------------------------------------------------
SO
CS Hold 5 Items are being ordered in less than Full
Master Case Qty. Emailed customer to revise PO. JO 4-23

Open in new window


I have now realized that there are no CR/LF characters at the end of some lines and the resulting output is mashed together.

Can we ..... look at each line, if 44 or less characters in length, append a CR/LF before concatonating the lines?
0
Comment
Question by:JDCam
5 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39175806
>>Can we ..... look at each line, if 44 or less characters in length, append a CR/LF before concatonating the lines?

Yes, if this is what you want:
EXTRACT(XMLAGG(XMLELEMENT("s", case when length(line_text)<44 then line_text || chr(10) else line_text end) order by line_num asc)

Are you sure this is what you want?  It doesn't seem valid to me.

If you use the code I posted in the other question to add a space character, you could always remove duplicate spaces with a simple regex:

regexp_replace(your_final_output,'[ ]+',' ')
0
 
LVL 1

Author Closing Comment

by:JDCam
ID: 39175962
Yes, its pretty good!

A little confusing to explain, but the application allows input at 70 char per line, then stores it in a column that is only 45 char (no idea!). Characters beyond 45 are inserted to a new row. If full lines of text were entered, the result is alternating rows of 45 and 15.  this combined with the fact they may have actually typed short lines and used CR/LF makes in near impossible to re-construct with accuarcy.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39176471
may I ask, are there any other fields in this comment table (e.g. a datetime)?

I'm just wondering if there's a way to use of lead/lag which might produce a better result, this is not what you are looking for, but it might give rise to ideas
select
  ord_num
, line_num
, LAG (line_comment) OVER (partition by ord_num order by line_num) lag_1
, line_comment
, LEAD (line_comment) OVER (partition by ord_num order by line_num) lead_1
from ord_comment

Open in new window

http://sqlfiddle.com/#!4/072bc/1
Maybe if used on a larger sample of your data  (with CR/LFs) it might reveal something(?)
(curious method of comment storage v.odd)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39176609
this might be helpful? it doesn't attempt a concatenation, but it may lead to placing the CR's appropiately (? maybe)
select
  ord_num
, case
      when len_lead = 45 and len_lag = 0   then line_comment
      when len_lag  = 45                   then lag1 || line_comment
      when len_this < 45 and len_lead < 45 then line_comment
  end as concat_comment
from (
      select
        ord_num
      , line_num
      , LAG (line_comment) OVER (partition by ord_num order by line_num) lag1
      , line_comment
      , LEAD (line_comment) OVER (partition by ord_num order by line_num) lead1
      , NVL(length(LAG (line_comment) OVER (partition by ord_num order by line_num)),0) len_lag
      , length(line_comment) len_this
      , NVL(length(LEAD (line_comment) OVER (partition by ord_num order by line_num)),0) len_lead
      from ord_comment
     )    
where case
        when len_lead = 45 and len_lag = 0 then 1
        when len_lag  = 45 then 1
        when len_this < 45 and len_lead < 45 then 1
      end = 1

Open in new window

& I'm not going to claim it's highly efficient either. http://sqlfiddle.com/#!4/3a33c/2
0
 
LVL 32

Expert Comment

by:awking00
ID: 39177288
>>I have now realized that there are no CR/LF characters at the end of some lines and the resulting output is mashed together. <<

Does that mean there could be some lines less than 44 characters that do contain CR/LF characters?
If so, the accepted solution would result in double linefeeds, but could be simply modified to avoid such a scenario -
EXTRACT(XMLAGG(XMLELEMENT("s", case when length(line_text)<44 and instr(line_text,chr(10)) = 0 then line_text || chr(10) else line_text end) order by line_num asc)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question