Solved

Oracle 10g - Concatonate Multiple Rows - PART 2

Posted on 2013-05-17
5
552 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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
 

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 48

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 48

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 31

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now