Duplicate_Table_Alias

View All Public Scripts Login to Run Script

Script Name Duplicate_Table_Alias
Visibility Unlisted - anyone with the share link can access
Description When the same table alias is used to refer a table or view in the join list as well as a "with" view, priority is given to the former. Is this expected? If so, why?
Area SQL General / SQL Query
Contributor techplusdata@gmail.com
Created Tuesday February 02, 2021

Statement 1

create table bibtest (a number, b number, c number)

Table created.

Statement 2

create table bibtest (a number, b number, c number)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 3

create table bibtest_2 (a number, b number, c number)

Table created.

Statement 4

create table bibtest_2 (a number, b number, c number)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 5

create table bibtest (a number, b number, c number)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 6

create table bibtest_2 (a number, b number, c number)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 7
```
select * from bibtest
```
```
no data found
```

Statement 8

insert into bibtest select rownum, rownum, rownum from dual connect by level <= 10

10 row(s) inserted.

Statement 9

insert into bibtest_2 select rownum, rownum, rownum from dual connect by level <= 10

10 row(s) inserted.

Statement 10

select * from bibtest


A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10

10 rows selected.

A	B	C
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10

Statement 11

select * from bibtest_2


A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10

10 rows selected.

A	B	C
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10

Statement 12

select * from bibtest_2


A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10

10 rows selected.

A	B	C
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10

Statement 13

with t1 as (select 'DUMMY' X from dual) 
select t2.* from bibtest t1, bibtest_2 t2 
 where t1.a = t2.a


A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10

10 rows selected.

A	B	C
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10

Statement 14

with t1 as (select 'DUMMY' X from dual) 
select * from bibtest t1, bibtest_2 t2 
 where t1.a = t2.a


A B C A B C
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5
6 6 6 6 6 6
7 7 7 7 7 7
8 8 8 8 8 8
9 9 9 9 9 9
10 10 10 10 10 10

10 rows selected.

A	B	C	A	B	C
1	1	1	1	1	1
2	2	2	2	2	2
3	3	3	3	3	3
4	4	4	4	4	4
5	5	5	5	5	5
6	6	6	6	6	6
7	7	7	7	7	7
8	8	8	8	8	8
9	9	9	9	9	9
10	10	10	10	10	10

Statement 15

with t1 as (select 'DUMMY' X from dual) 
select * from (select c from bibtest) t1, 
              bibtest_2 t2 
 where t1.a = t2.a

ORA-00904: "T1"."A": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 16

with t1 as (select * from bibtest) 
select * from (select c from bibtest) t1, 
              bibtest_2 t2 
 where t1.a = t2.a

ORA-00904: "T1"."A": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

A	B	C	A	B	C
1	1	1	1	1	1
2	2	2	2	2	2
3	3	3	3	3	3
4	4	4	4	4	4
5	5	5	5	5	5
6	6	6	6	6	6
7	7	7	7	7	7
8	8	8	8	8	8
9	9	9	9	9	9
10	10	10	10	10	10

A	B	C	A	B	C
1	1	1	1	1	1
2	2	2	2	2	2
3	3	3	3	3	3
4	4	4	4	4	4
5	5	5	5	5	5
6	6	6	6	6	6
7	7	7	7	7	7
8	8	8	8	8	8
9	9	9	9	9	9
10	10	10	10	10	10

A	B	C	A	B	C
1	1	1	1	1	1
2	2	2	2	2	2
3	3	3	3	3	3
4	4	4	4	4	4
5	5	5	5	5	5
6	6	6	6	6	6
7	7	7	7	7	7
8	8	8	8	8	8
9	9	9	9	9	9
10	10	10	10	10	10