Home » SQL & PL/SQL » SQL & PL/SQL » Suggestion on Connect By query (Oracle 10g)
Suggestion on Connect By query [message #353676] Tue, 14 October 2008 10:31 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

create table test(file_name varchar2(100), env_type_code varchar2(10), package_id number, revision number);

select * from test
							   
insert ALL 
into test values('SCHEDULER.pks','FO',36177,111002)
into test values('SCHEDULER.pks','FO',36113,111002)
into test values('alter_order_header_add_dam_id.sql','FO',36177,624511)
into test values('alter_order_header_add_dam_id.sql','FO',36113,624511)	
SELECT * FROM DUAL;	

SQL> SELECT x.file_name
  2                ,x.env_type_code, x.revision
  3        ,MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.package_id),','),2)) package_string
  4  FROM   (SELECT t.file_name
  5                ,t.env_type_code, package_id, revision
  6                ,ROW_NUMBER() OVER (PARTITION BY t.file_name, t.env_type_code,t.revision ORDER BY t.file_name, t.env_type_code,t.revision) curr
  7                ,ROW_NUMBER() OVER (PARTITION BY t.file_name, t.env_type_code,t.revision ORDER BY t.file_name, t.env_type_code,t.revision) - 1  prev
  8          FROM   test t) x
  9  GROUP BY   x.file_name
 10                ,x.env_type_code, x.revision
 11  START WITH x.curr = 1
 12  CONNECT BY x.prev = PRIOR x.curr AND  x.file_name   = PRIOR x.file_name
 13  /  

FILE_NAME                                                                                            ENV_TYPE_C   REVISION
---------------------------------------------------------------------------------------------------- ---------- ----------
PACKAGE_STRING
--------------------------------------------------------------------------------------------------------------------------------------------------------
alter_order_header_add_dam_id.sql                                                                    FO             624511
36177,36113

SCHEDULER.pks                                                                                        FO             111002
36177,36113



Just wanted to know of this is correct way of getting the package_string as comma seperated or there can be a better way of doing it?

Re: Suggestion on Connect By query [message #353680 is a reply to message #353676] Tue, 14 October 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is one way there are many others with stragg, wm_concat, xmlconcat...

Regards
Michel
Re: Suggestion on Connect By query [message #353687 is a reply to message #353676] Tue, 14 October 2008 11:14 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel, can you please give an example with XMLCONCAT?
Re: Suggestion on Connect By query [message #353688 is a reply to message #353687] Tue, 14 October 2008 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just search here there are many examples.

Regards
Michel
Re: Suggestion on Connect By query [message #353691 is a reply to message #353676] Tue, 14 October 2008 11:24 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Sorry michel.. searched but couldn't find which is related to this? Can you please help?
Re: Suggestion on Connect By query [message #353693 is a reply to message #353691] Tue, 14 October 2008 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I wanted to say xmlagg. xmlconcat was the old way.

Regards
Michel
Re: Suggestion on Connect By query [message #353746 is a reply to message #353693] Tue, 14 October 2008 23:53 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Tue, 14 October 2008 22:38
I wanted to say xmlagg. xmlconcat was the old way.

Regards
Michel



Yeah that worked for me Smile .. Thanks Michel

SQL> select t.file_name
  2         ,t.env_type_code, t.revision, rtrim (extract (xmlagg (xmlelement ("Z", package_id ||',')), '/Z/text()'), ',') a
  3  from test t
  4  group by t.file_name
  5          ,t.env_type_code, t.revision
  6  /

FILE_NAME                                                                                            ENV_TYPE_C   REVISION
---------------------------------------------------------------------------------------------------- ---------- ----------
A
------------------------------------------------------------------------------------------------------------------------------
SCHEDULER.pks                                                                                        FO             111002
36177,36113

alter_order_header_add_dam_id.sql                                                                    FO             624511
36177,36113

Previous Topic: trying to find out table name based on a value
Next Topic: ORA-01722 Error
Goto Forum:
  


Current Time: Sat Dec 10 16:27:30 CST 2016

Total time taken to generate the page: 0.07461 seconds