SQL & PL/SQL

10g

HI,

I have a couple of questions to ask. Please see below that I have provided a script to re-create my problem.

Question 1.

I have the following query that will not run when I include columns (see 1.2) in the select but will run when I use the asterix symbol (see 1.1)

*1.1 Runs with no problems*

select *
  from ora full outer join txt on ora.ora_id_y = txt.txt_id_y;

*1.2 Returns error.*

select txt.txt_id_y
     , txt.txt_n
     , txt.txt_y
     , ora.ora_n
     , ora.ora_y
  from ora full outer join txt on ora.ora_id_y = txt.txt_id_y;

Error report:
SQL Error: ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:

Question 2.

Is it possible to use one query with self joins and using multiple full outer joins to accomplish what I have below which is 6 view to get to the query that I have in Question 1.

CREATE SCRIPT

drop table master;

create table master
( id varchar2 (10 char)
, txt varchar2 (1 char)
, ora varchar2 (1 char)
);

INSERT INTO MASTER (ID, TXT, ORA) VALUES ('orange' , 'Y', 'Y');
INSERT INTO MASTER (ID, TXT, ORA) VALUES ('apple', 'Y', '');
INSERT INTO MASTER (ID, TXT, ORA) VALUES ('orange'  , 'Y', 'N');
INSERT INTO MASTER (ID, TXT, ORA) VALUES ('peach'  , 'Y', '');

create or replace force view ora_n
as
select id
     , count (ora) ORA_N
  from master
 where ora in ('N')
 group by id;

create or replace force view ora_y
as
 select id
     , count (ora) ORA_Y
  from master
 where ora in ('Y')
 group by id;

create or replace force view txt_n
as 
 select id
     , count (txt) TXT_N
  from master
 where txt in ('N')
 group by id;

create or replace force view txt_y
as 
 select id
       , count (txt) TXT_Y
  from master
 where txt in ('Y')
 group by id;
 
create or replace force view ora
as 
select ora_n.id ora_id_n
     , ora_y.id ora_id_y
     , ora_n.ora_n
     , ora_y.ora_y
  from ora_n full outer join ora_y on ora_n.id = ora_y.id;

create or replace force view txt
as 
select txt_y.id txt_id_n
     , txt_y.id txt_id_y
     , txt_n.txt_n
     , txt_y.txt_y
  from txt_n full outer join txt_y on txt_n.id = txt_y.id; 

Edited by: benton on Aug 21, 2012 10:48 AM

Edited by: benton on Aug 21, 2012 11:11 AM