Home » SQL & PL/SQL » SQL & PL/SQL » Proble with hierarchy query (merged 3)
Proble with hierarchy query (merged 3) [message #381637] Mon, 19 January 2009 01:20 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi All,

I tried to execute the following cases, Case 1 and 2.
For case 1, I received an error , but after replacing ',' = > '~' in Case 2, no error is shown.

Please help me in this.


SELECT * FROM v$version ;

BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production                          
CORE	10.2.0.4.0	Production                                      
TNS for Solaris: Version 10.2.0.4.0 - Production                
NLSRTL Version 10.2.0.4.0 - Production                          

5 rows selected.


This is what I tried.

SET LINE 150
COLUMN PRINT_NAME FORMAT A20
COLUMN PATH FORMAT A80
SET ECHO ON

SQL> select print_name FROM tqits20_tfm_sec_user
     WHERE user_id = 16470

PRINT_NAME          
--------------------
Matthews, Markus    

1 row selected.

SQL> --Case 1.
SQL> SELECT     print_name,SYS_CONNECT_BY_PATH (print_name, ',') PATH
      FROM tqits20_tfm_sec_user
     WHERE user_id = 16470
CONNECT BY NOCYCLE PRIOR user_id = manager_id AND LEVEL <= 10

ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value

SQL> -- Case 2
SQL> SELECT     print_name,SYS_CONNECT_BY_PATH (print_name, '~') PATH
      FROM tqits20_tfm_sec_user
     WHERE user_id = 16470
CONNECT BY NOCYCLE PRIOR user_id = manager_id AND LEVEL <= 10

PRINT_NAME           PATH                                                                            
-------------------- --------------------------------------------------------------------------------
Matthews, Markus     ~Matthews, Markus                                                               
Matthews, Markus     ~Morgan, Graham~Matthews, Markus                                                
Matthews, Markus     ~James, Andrew Terrence (Old Users)~Morgan, Graham~Matthews, Markus             

3 rows selected.


I think the error is with using ',' in the SYS_CONNECT_BY_PATH, If it is so , how can I resolve it.

Thanks
Trivendra
Re: Proble with hierarchy query (merged 3) [message #381643 is a reply to message #381637] Mon, 19 January 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
 *Cause:
 *Action: Use another seperator which does not occur in any column value,
          then retry.

Use REPLACE.

Regards
Michel
Re: Proble with hierarchy query (merged 3) [message #381646 is a reply to message #381643] Mon, 19 January 2009 01:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel,

I could miss other seperator, which exists for other user_id.

Thanks once again and sorry for multi post.

Regards
Trivendra
Re: Proble with hierarchy query (merged 3) [message #381649 is a reply to message #381646] Mon, 19 January 2009 02:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use any character that is not in your data. For instance, a line feed or a not printable character.

Regards
Michel
Previous Topic: Getting the Prior Dimension Value
Next Topic: PL/SQL Cursor
Goto Forum:
  


Current Time: Sat Dec 03 08:19:53 CST 2016

Total time taken to generate the page: 0.10414 seconds