Home » Developer & Programmer » Forms » Help me to write a Query
Help me to write a Query [message #80688] Thu, 31 October 2002 23:19 Go to next message
Viswa
Messages: 8
Registered: October 2002
Junior Member
Hi

I have a table like below......

Src_id Mrg_id Final
=========================
101 102
102 103
103 104
104 105

here 101=102=103=104=105... so the third column will be filled with 105..

How do write a query?

Bye
Viswa
Re: Help me to write a Query [message #80690 is a reply to message #80688] Fri, 01 November 2002 07:21 Go to previous messageGo to next message
Julie
Messages: 98
Registered: February 2002
Member
update table
set final = (select max(mrg_id) from table)
Re: Help me to write a Query [message #80699 is a reply to message #80688] Mon, 04 November 2002 05:53 Go to previous messageGo to next message
F. Tollenaar
Messages: 64
Registered: November 2002
Member
I presume there is a row with src_id = 105 and mgr_id is null.

SQL> drop table hierarchy
  2  /

Table dropped.

SQL> create table hierarchy (id number primary key, mgr_id number
  2  , final number);

Table created.

SQL> alter table hierarchy add constraint mgr_fk
  2  foreign key (mgr_id) references hierarchy (id);

Table altered.

SQL> insert into hierarchy values(106, null, null);

1 row created.

SQL> insert into hierarchy values(105, null, null);

1 row created.

SQL> insert into hierarchy values(104, 105, null);

1 row created.

SQL> insert into hierarchy values(103, 104, null);

1 row created.

SQL> insert into hierarchy values(102, 103, null);

1 row created.

SQL> insert into hierarchy values(101, 102, null);

1 row created.

SQL> commit;

Commit complete.

SQL> update hierarchy h3
  2  set h3.final = (
  3  select substr((select max(to_char(level, 'fm0000')||h2.id)
  4                from   hierarchy h2
  5                start  with h2.id = h1.id
  6                connect by prior h2.mgr_id = h2.id), 5)
  7  from   hierarchy h1
  8  where  h1.id = h3.id)
  9  /

6 rows updated.

SQL> select * from hierarchy;

        ID     MGR_ID      FINAL                                                
---------- ---------- ----------                                                
       106                   106                                                
       105                   105                                                
       104        105        105                                                
       103        104        105                                                
       102        103        105                                                
       101        102        105                                                

6 rows selected.

SQL> spool off
Re: Help me to write a Query [message #80703 is a reply to message #80688] Mon, 04 November 2002 20:59 Go to previous message
Viswa
Messages: 8
Registered: October 2002
Junior Member
Hi F. Tollenaar,
Thanx a lot for your Suggetion......
Previous Topic: Exporting Data from EXCEL file into ORACLE TABLE
Next Topic: Forms4.5/Reports 2.5 on Windows 2000 with Oracle 9i
Goto Forum:
  


Current Time: Sat Mar 02 20:56:47 CST 2024