Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior
icon9.gif  connect by prior [message #313715] Mon, 14 April 2008 05:05 Go to next message
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 Confused

Thanks
Re: connect by prior [message #313718 is a reply to message #313715] Mon, 14 April 2008 05:21 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hint :Use the table twice in the FROM clause.

regards,
Re: connect by prior [message #313720 is a reply to message #313715] Mon, 14 April 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no "connect by" just a self-join.

Regards
Michel
Re: connect by prior [message #313730 is a reply to message #313715] Mon, 14 April 2008 06:27 Go to previous message
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.
Previous Topic: Cursor is not taking the not in class and NOT IN IS Mal Functioning (merged 2 threads)
Next Topic: UTL_FILE Directory
Goto Forum:
  


Current Time: Sun Dec 04 10:46:38 CST 2016

Total time taken to generate the page: 0.04253 seconds