Home » SQL & PL/SQL » SQL & PL/SQL » how can we create table by using self-join?
how can we create table by using self-join? [message #416531] Mon, 03 August 2009 04:57 Go to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

hi all can anyone guide me how can we create self-join tables and how can we use by prior?

please guide me if anyone wants
sarah
Re: how can we create table by using self-join? [message #416535 is a reply to message #416531] Mon, 03 August 2009 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your question is meaningless as it.

Regards
Michel
Re: how can we create table by using self-join? [message #416536 is a reply to message #416531] Mon, 03 August 2009 05:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
create self-join

You can use self-join on the table containing fields empid and managerid. And many others also.

Quote:
how can we use by prior?

Search 'Hierarchical query in Oracle' in Google.

regards,
Delna
Re: how can we create table by using self-join? [message #416538 is a reply to message #416536] Mon, 03 August 2009 05:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but your answer is as meaningless as the question was.
In fact you can self-join any table on any column if you would like to do so.
Re: how can we create table by using self-join? [message #416540 is a reply to message #416538] Mon, 03 August 2009 05:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Frank sir,
Quote:
In fact you can self-join any table on any column if you would like to do so.


Correct ,but not meaningful.

regards,
Delna
Re: how can we create table by using self-join? [message #416542 is a reply to message #416540] Mon, 03 August 2009 05:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not meaningful?
How is this not meaningful:
select first.id
from   table_a first
,      table_a second
where  first.col_1 = second.col_2
and    first.id   != second.id


Just a simple example, but it should show what I mean.
Re: how can we create table by using self-join? [message #416547 is a reply to message #416542] Mon, 03 August 2009 05:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Frank sir,
can you please explain using real example?

regards,
Delna
Re: how can we create table by using self-join? [message #416551 is a reply to message #416547] Mon, 03 August 2009 05:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This was a real example, except that I made a typo.
Should have been:
select first.id
from   table_a first
,      table_a second
where  first.col_1 = second.col_1
and    first.id   != second.id

Shows all rows in table_a that have duplicate values in col_1.

A self join means you join a table to itself.
I think what you are talking about is a hierarchical query.
Re: how can we create table by using self-join? [message #416554 is a reply to message #416551] Mon, 03 August 2009 05:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Shows all rows in table_a that have duplicate values in col_1.


Ok sir. I got it.

And was not talking only about hierarchical queries.

regards,
Delna
Re: how can we create table by using self-join? [message #416583 is a reply to message #416554] Mon, 03 August 2009 06:58 Go to previous messageGo to next message
furqanhussain
Messages: 6
Registered: June 2009
Location: Karachi
Junior Member

i have mentiond below actualy query which to use in realy time.


select lpad(ename,level*5) from emp
connect by prior empno = mgr
start with ename = 'KING'

you also change ENAME& the query get his subordinate.

Rgd
Furqan Hussain
Re: how can we create table by using self-join? [message #416587 is a reply to message #416583] Mon, 03 August 2009 07:16 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

actually i have created table but i am confused about prior and its also givng me an error by using sql.
here is the table

create table hr(
Employee_code varchar2(10),
Name varchar2(30),
Fname varchar2(30),
Gender varchar2(10),
Marital_status varchar2(10),
Job varchar2(10),
Sal varchar2(10),
DOB date,
master number,
country varchar2(10),
Nationality varchar2(10),
address varchar2(40),
constraint hr_pk primary key(employee_code),
constraint hr_fk foreign key(master) references hr(employee_code));

i made one data block name "control"then i made tree name "emp_tree"
and i use data query there
here is the query: select 1,level,name,fname,gender,job,sal from hr connect by prior employee_id = master start with master is null
after that in the form level i create WHEN-NEW-FORM-INSTANCE trigger
here is the code:FTREE.POPULATE_TREE('CONTROL.EMP_TREE');

guide me more if there is any mistake or there is any problem in table please help me

sarah
Re: how can we create table by using self-join? [message #416589 is a reply to message #416531] Mon, 03 August 2009 07:21 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Telling us what the error is would be a good start. Using code tags would be appreciated as well.
What happens if you run the query in SQLplus?

Actually that fk won't work - master is a number, employee_code is a varchar.
Re: how can we create table by using self-join? [message #416591 is a reply to message #416587] Mon, 03 August 2009 07:32 Go to previous messageGo to next message
furqanhussain
Messages: 6
Registered: June 2009
Location: Karachi
Junior Member

Query is fine & you can run in sql the will fetch record but the problem in you form which is not calling proper FTREE.POPULATE_TREE package.



Rgd
Furqan Hussain
Re: how can we create table by using self-join? [message #416596 is a reply to message #416589] Mon, 03 August 2009 08:04 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

when i query in sql its giving me No row selected.

sarah
Re: how can we create table by using self-join? [message #416611 is a reply to message #416531] Mon, 03 August 2009 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you need to get the query working in sqlplus before bothering integrating it with forms.
The first thing you need to do is fix the foreign key. The datatypes of master and employee_code need to be the same. Otherwise this happens:
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 3 14:44:10 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> INSERT INTO hr(name,fname,gender,job,sal, Employee_code, MASTER)
VALUES ('cookie', 'monster', 'male', 'manager', 10, 1, NULL);  2

1 row created.

SQL> INSERT INTO hr(name,fname,gender,job,sal, Employee_code, MASTER)
VALUES ('random', 'grunt', 'male', 'coder', 5, 2, 1);  2
INSERT INTO hr(name,fname,gender,job,sal, Employee_code, MASTER)
*
ERROR at line 1:
ORA-02291: integrity constraint (LIVE.HR_FK) violated - parent key not found


SQL>
Re: how can we create table by using self-join? [message #416612 is a reply to message #416531] Mon, 03 August 2009 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Interestingly enough this exposes an inconsistency in oracle. I could have sworn when I looked at this oracle wouldn't let you create the fk as listed above - seems I was half right.

If you run the create table the OP supplied it'll create sucessfully and create the fk:
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 3 14:58:42 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> create table hr(
  2  Employee_code varchar2(10),
  3  Name varchar2(30),
  4  Fname varchar2(30),
  5  Gender varchar2(10),
  6  Marital_status varchar2(10),
  7  Job varchar2(10),
  8  Sal varchar2(10),
  9  DOB date,
 10  master number,
 11  country varchar2(10),
 12  Nationality varchar2(10),
 13  address varchar2(40),
 14  constraint hr_pk primary key(employee_code),
 15  constraint hr_fk foreign key(master) references hr(employee_code));

Table created.


SQL> select constraint_name from user_constraints where table_name = 'HR' and constraint_type = 'R';

CONSTRAINT_NAME
------------------------------
HR_FK


However if you recreate the table without the fk and then try to add the fk with an alter table command you get this:
SQL> drop table hr;

Table dropped.

SQL> create table hr(
  2  Employee_code varchar2(10),
  3  Name varchar2(30),
  4  Fname varchar2(30),
  5  Gender varchar2(10),
  6  Marital_status varchar2(10),
  7  Job varchar2(10),
  8  Sal varchar2(10),
  9  DOB date,
 10  master number,
 11  country varchar2(10),
 12  Nationality varchar2(10),
 13  address varchar2(40),
 14  constraint hr_pk primary key(employee_code));

Table created.

SQL> ALTER TABLE hr ADD CONSTRAINT hr_fk foreign key(master) references hr(employee_code);
ALTER TABLE hr ADD CONSTRAINT hr_fk foreign key(master) references hr(employee_code)
                                                *
ERROR at line 1:
ORA-02267: column type incompatible with referenced column type


SQL>
Re: how can we create table by using self-join? [message #416615 is a reply to message #416531] Mon, 03 August 2009 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or 99% right even. That only happens with self-referential foreign keys.
Re: how can we create table by using self-join? [message #416617 is a reply to message #416615] Mon, 03 August 2009 09:27 Go to previous message
Sarah Aman
Messages: 80
Registered: July 2009
Member

thanks alot i got the point and i solved my problem now once again thanks alot
sarah
Previous Topic: Greatest Date
Next Topic: query not returning values
Goto Forum:
  


Current Time: Mon Dec 05 19:17:20 CST 2016

Total time taken to generate the page: 0.22836 seconds