• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1630
  • Last Modified:

Need help on sys_connect_by_path

Hi,
I am using sys_connect_by_path function.
the field from which information is fetched contains special character which sys_connect_by_path doesnt support.
and because of which sql is erroring out,
Could you please help as to how to remove the special character for sys_connect_by_path funct,,
Below is the sql m using.. Please help
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         (FIELD NAME), ' > ')), ' > ') path
  from TABLE NAME
where CONDITION
start with
CONDITION
connect by prior
     CONDITION

0
Techygal
Asked:
Techygal
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of oracle are you using? you posted in Oracle 8 zone, for which version that function is NOT available...
0
 
TechygalAuthor Commented:
Oracle 8i..thats Release 8.1.7.0.0 ..
does this helps..
Function is working fine.. bt it is used only for varchar.. char.. n all of those.. bt if the field contains special character .. its erroring out...

so wat can I use to remove the special character when its been selected..
 
I have pasted the SQL on my question..
Kindly hav a look at it
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the field from which information is fetched contains special character which sys_connect_by_path doesnt support.

which are those characters?

you could "translate" those characters, for example if "%&/()=" are characters not working, translate them "away":
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         ( TRANSLATE(FIELD NAME, '%&/()=' ,' ' )), ' > ')), ' > ') path
  from TABLE NAME
where CONDITION
start with
CONDITION
connect by prior
     CONDITION

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TechygalAuthor Commented:
Thank U so MUCH..... i did try translate. bt wat went wrong was the replacing value,,  i used '' rather ' '( space was missin)... :)..

you are really great.. appreciate your help..


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help. and yes, the ' ' instead of '' in oracle is important, as '' is implicitly considered NULL, and then TRANSLATE does not do anything.
0
 
TechygalAuthor Commented:
Good Job... :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now