?
Solved

Oracle 10g - Concatonate Multiple Rows - PART 2

Posted on 2013-05-17
5
Medium Priority
?
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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