troubleshooting Question

how to convert comma separated row into columns

Avatar of sakthikumar
sakthikumar asked on
Oracle Database
6 Comments2 Solutions1384 ViewsLast Modified:
I have a value like below

D@#Direct, I@#Indirect

First I want to separate this into two rows like below and get description based on value.
D@#Direct
I@#Indirect

in which Direct and Indirect are description for values I and D.

I used this query to extract the description which does not work in 10 g.

WITH T AS

(SELECT VALUE_X FROM gcb_dba.GCB_ADMIN S WHERE S.ITEM = 'Delivery Mode')

SELECT DESCR --INTO LV_DESCR

  FROM (SELECT SUBSTR(DESCR, 1,INSTR(DESCR, '@#') - 1) VALUE1,

               SUBSTR(DESCR, INSTR(descr, '@#') + 2) DESCR

          FROM (SELECT CAST(TRIM(EXTRACT(COLUMN_VALUE, '/e/text()')) AS

                            VARCHAR2(100)) DESCR

                  FROM T X,

                       TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>' || ---

                                                         REPLACE(value_x,

                                                                 ',',

                                                                '</e><e>') ||
                                                         '</e></ROW>'),
                                                 '//e')))))

                                                  WHERE VALUE1 = 'D';                                                

=================================

I can execute the below in 10g to convert a value into rows, by commenting some part.
but not execute as a whole to extract description. I am getting error
"end of file on communication channel"


WITH T AS

(SELECT VALUE_X FROM gcb_dba.GCB_ADMIN S WHERE S.ITEM = 'Delivery Mode')

SELECT DESCR --INTO LV_DESCR

  FROM (/*SELECT SUBSTR(DESCR, 1,INSTR(DESCR, '@#') - 1) VALUE1\*,

               SUBSTR(DESCR, INSTR(descr, '@#') + 2) DESCR*\

          FROM (*/SELECT CAST(TRIM(EXTRACT(COLUMN_VALUE, '/e/text()')) AS

                            VARCHAR2(100)) DESCR

                  FROM T X,

                       TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>' || ---

                                                         REPLACE(value_x,

                                                                 ',',

                                                                 '</e><e>') ||

                                                         '</e></ROW>'),

                                                 '//e'))))
 
==========================================================
Now I want to know why in 10g I am not able to execute the query
and any other alternative way to extract the description.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros