Home » SQL & PL/SQL » SQL & PL/SQL » String too long (Concatenation) (Ora 10g)
String too long (Concatenation) [message #302994] Wed, 27 February 2008 09:22 Go to next message
ph1975
Messages: 7
Registered: February 2008
Location: Z├╝rich
Junior Member
Hi Folks,
i am doing a sys_connect_by_path query in which i am concatenating rows into one single column.
Of course Wink i am running into a String too long problem.

Do you have any idea how to avoid this limitation?

SQL:

select
  account_no
  , replace(sys_connect_by_path(
                        chg_date || ' --- ' || note_code || ' --- ' || rtrim(chg_who,' ') || ' --- '  || chr(13) || 
                        decode(length(trim(note_text)),null,'', '    Note text: ' || note_text || chr(13)) || 
                        '    Comment  : ' || comments || chr(13)|| chr(13), '$'),'$','') descr
from 
  (
    select 
      account_no
      , chg_date
      , chg_who
      , comments
      , note_text
      , note_code
      , row_number() over (partition by account_no order by chg_date desc) val_index
    from 
      tmp_bp_cmf_notes
    where
      account_no > 1000000
  )
where
  connect_by_isleaf = 1
connect by
  val_index = prior val_index + 1
  and account_no = prior account_no
start with 
  val_index = 1
Re: String too long (Concatenation) [message #303002 is a reply to message #302994] Wed, 27 February 2008 09:54 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way in SQL.

Regards
Michel
Previous Topic: WITH Clause
Next Topic: Returning only the first12 records returned by a query
Goto Forum:
  


Current Time: Sun Dec 04 04:15:43 CST 2016

Total time taken to generate the page: 0.05259 seconds