connect by prior [message #313715] |
Mon, 14 April 2008 05:05  |
cherry
Messages: 56 Registered: December 2007
|
Member |
|
|
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.1.0.2.0 - Production
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> create table users (userid number, name varchar2(10), createdby number);
insert into users values (1, 'mike', 2);
insert into users values (2, 'admin', 0);
insert into users values (0, 'system', 0);
insert into users values (3, 'freddy', 2);
insert into users values (4, 'Naggie', 1);
Expected output
userid name updatebyid updatedbyname
------ ------ ------------ -------------
1 mike 2 admin
2 admin 0 system
3 freddy 2 admin
.
.
I can write a function to get the name & use it in the select clause, but I prefer doing it in SQL.
I was able to build the SQL yday with connect by prior clause. Dont seem to get it correct today
Thanks
|
|
|
|
|
|
|
|
| Re: connect by prior [message #313730 is a reply to message #313715] |
Mon, 14 April 2008 06:27  |
cherry
Messages: 56 Registered: December 2007
|
Member |
|
|
|
Thanks. Will try t'row with self join/ansi join. Its so simple. Actually, this was a portion of a larger query which I thought I had troubles with. Any way, thanks again.
|
|
|
|