Home » SQL & PL/SQL » SQL & PL/SQL » Multiple rows into Single row on Oracle 8i
Multiple rows into Single row on Oracle 8i [message #223175] Wed, 07 March 2007 20:43 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
I want to run the following script in Oracle 8i for displaying multiple rows into single row. I am getting error message invalid column sys_connect_by_path, please can anyone help me.

select
lic_no,
substr(SYS_CONNECT_BY_PATH(ro_no, ','),2) ro_no
from
(
select
ro_no,
lic_no,
count(*) OVER ( partition by lic_no ) cnt,
ROW_NUMBER () OVER ( partition by lic_no order by ro_no) seq
from MSS_V_CUST_RETENTN
where
lic_no is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
LIC_NO=LIC_NO;
Re: Multiple rows into Single row on Oracle 8i [message #223205 is a reply to message #223175] Thu, 08 March 2007 00:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SYS_CONNECT_BY_PATH is a 9i feature.
Re: Multiple rows into Single row on Oracle 8i [message #223210 is a reply to message #223205] Thu, 08 March 2007 00:37 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

There is a way to make it work (unsupported by Oracle)

It's up to you if you want to use it.

alter session set "_new_connect_by_enabled"=TRUE;
Re: Multiple rows into Single row on Oracle 8i [message #223226 is a reply to message #223210] Thu, 08 March 2007 01:23 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The problem with that last solution is that you might end up without Oracle support if you fiddle with unsupported parameters without having the blessing of Oracle. Tom also thinks it's a bad idea...

MHE
Previous Topic: Get first row with aggregate function?
Next Topic: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function.
Goto Forum:
  


Current Time: Sat Dec 10 16:41:50 CST 2016

Total time taken to generate the page: 0.09780 seconds