SQL

[LogicallySkewed]

Hi,

I am new to SQL. I wrote a query to pull data from an Oracle database and can't get it to work. When I run it I get two error messages:

ERROR: ORACLE prepare error: ORA-00918: column ambiguously defined.
ERROR: SQL View WORK.ADM could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

Here is my code:

PROC SQL;
CONNECT TO oracle (user=SOCHA orapw=westwind02 path=PPRD);
CREATE VIEW Adm AS
SELECT * FROM connection to oracle
(SELECT saradap.saradap_pidm,
saradap.saradap_term_code_entry,
stvlevl.stvlevl_desc,
saradap.saradap_appl_date,
stvapst.stvapst_desc,
stvadmt.stvadmt_desc,
stvstyp.stvstyp_desc,
stvcoll.stvcoll_desc,
stvdegc.stvdegc_desc,
stvmajr.stvmajr_desc,
stvmajr.stvmajr_cipc_code,
stvresd.stvresd_desc,
stvresd.stvresd_in_state_ind,
stvterm.stvterm_desc,
stvacyr.stvacyr_desc,
saradap.saradap_full_part_ind,
stvapdc.stvapdc_desc,
stvsite.stvsite_desc,
stvsbgi.stvsbgi_type_ind,
stvsbgi.stvsbgi_srce_ind,
stvsbgi.stvsbgi_desc,
stvcamp.stvcamp_desc,
stvdept.stvdept_desc
FROM SATURN.saradap LEFT JOIN SATURN.sabsupl ON saradap.saradap_pidm=sabsupl.sabsupl_pidm AND saradap.saradap_term_code_entry=sabsupl.sabsupl_term_code_entry,
SATURN.sabsupl LEFT JOIN SATURN.sarappd ON sabsupl_pidm=sarappd_pidm AND sabsupl.sabsupl_term_code_entry=sarappd.sarappd_term_code_entry,
SATURN.saradap RIGHT JOIN SATURN.stvlevl ON saradap.saradap_levl_code=stvlevl.stvlevl_code,
SATURN.saradap RIGHT JOIN SATURN.stvapst ON saradap.saradap_apst_code=stvapst.stvapst_code,
SATURN.saradap RIGHT JOIN SATURN.stvadmt ON saradap.saradap_admt_code=stvadmt.stvadmt_code,
SATURN.saradap RIGHT JOIN SATURN.stvstyp ON saradap.saradap_styp_code=stvstyp.stvstyp_code,
SATURN.saradap RIGHT JOIN SATURN.stvcoll ON saradap.saradap_coll_code_1=stvcoll.stvcoll_code,
SATURN.saradap RIGHT JOIN SATURN.stvdegc ON saradap.saradap_degc_code_1=stvdegc.stvdegc_code,
SATURN.saradap RIGHT JOIN SATURN.stvmajr ON saradap.saradap_majr_code_1=stvmajr.stvmajr_code,
SATURN.saradap RIGHT JOIN SATURN.stvresd ON saradap.saradap_resd_code=stvresd.stvresd_code,
SATURN.stvterm RIGHT JOIN SATURN.stvacyr ON stvterm.stvterm_acyr_code=stvacyr.stvacyr_code,
SATURN.stvterm LEFT JOIN SATURN.saradap ON stvterm.stvterm_code=saradap.saradap_term_code_entry,
SATURN.sarappd LEFT JOIN SATURN.stvapdc ON sarappd.sarappd_apdc_code=stvapdc.stvapdc_code,
SATURN.saradap RIGHT JOIN SATURN.stvsite ON saradap.saradap_site_code=stvsite.stvsite_code,
SATURN.saradap RIGHT JOIN SATURN.stvsbgi ON saradap.saradap_sbgi_code=stvsbgi.stvsbgi_code,
SATURN.saradap RIGHT JOIN SATURN.stvcamp ON saradap.saradap_camp_code=stvcamp.stvcamp_code,
SATURN.saradap RIGHT JOIN SATURN.stvdept ON saradap.saradap_dept_code=stvdept.stvdept_code
GROUP BY saradap.saradap_pidm,
saradap.saradap_term_code_entry,
stvlevl.stvlevl_desc,
saradap.saradap_appl_date,
stvapst.stvapst_desc,
stvadmt.stvadmt_desc,
stvstyp.stvstyp_desc,
stvcoll.stvcoll_desc,
stvdegc.stvdegc_desc,
stvmajr.stvmajr_desc,
stvmajr.stvmajr_cipc_code,
stvresd.stvresd_desc,
stvresd.stvresd_in_state_ind,
stvterm.stvterm_desc,
stvacyr.stvacyr_desc,
saradap.saradap_full_part_ind,
stvapdc.stvapdc_desc,
stvsite.stvsite_desc,
stvsbgi.stvsbgi_type_ind,
stvsbgi.stvsbgi_srce_ind,
stvsbgi.stvsbgi_desc,
stvcamp.stvcamp_desc,
stvdept.stvdept_desc
ORDER BY saradap_pidm ASC);
DISCONNECT FROM oracle;
QUIT;
DATA Admissions;
SET Adm;
PROC CONTENTS DATA=Admissions;
RUN;

What am I doing wrong? Thanks in advanced.

 

[ChrisW75]

Holy Moly! That there is the mother of all joins! Personally I never get SAS to join more than 5 tables at a time. Believe it or not, it can actually be alot more efficient to break this up into a number of steps each joining the next table to the results of the previous one. Do a search on the DBKEY= option to improve performance.

Now, as the the problem with this step, surely the error message says it all?
ERROR: ORACLE prepare error: ORA-00918: column ambiguously defined.
This means that a column you've defined could be on more than one table, one thing to note is that I think what you've done is join SATURN.saradap to multiple tables, but referencing the SATURN.saradap brings it in multiple times. This leads to the above error because it isn't sure which instance of saradap to get the colunms from.
I think you should be doing something like this:-

 SATURN.saradap RIGHT JOIN SATURN.stvlevl ON saradap.saradap_levl_code=stvlevl.stvlevl_code,
           RIGHT JOIN SATURN.stvapst ON saradap.saradap_apst_code=stvapst.stvapst_code,
           RIGHT JOIN SATURN.stvadmt ON saradap.saradap_admt_code=stvadmt.stvadmt_code,

Also, are you sure you want to do a right join? A left join seems to make more sense..

ERROR: SQL View WORK.ADM could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

This says that a table or view you are referencing doesn't exist, check through for a typo.

Personally, I would seriously think about breaking this query up into pairs of joins, it makes it easier to read, easier to code, easier to debug. It can also be a leff of a lot more efficient.