topic Re: Column Found In More Than One Table In The Same Scope in SAS Enterprise Guide

Column Found In More Than One Table In The Same Scope

RichB — Tue, 11 Dec 2012 15:07:30 GMT

Hi All

I've come across a problem using Enterprise Guide 4.3 and wondered if there was a fix for it, I wonder if you lovely people may be able to help.

I've got a process that I'm trying to edit to replace one of the base data tables as the table that was being used doesn't contain enough historical data for the report I'm producing. I've used the query builder and swapped out the old table (t1) for the new one (t3). The data types are the same for the fields that I'm using but i'm getting a lot of the error "ERROR: Column t3.CompanyNo was found in more than one table in the same scope." with a lot of the same error for different columns. I've spoken to our resident SAS guru's in our head office but they can only suggest building the report again from scratch (which I'd really rather not do, it took a while to build).

I've attached the code as it's showing with the errors, if anyone could suggest a fix for this it would be fantastic.

Thanks

Rich

Re: Column Found In More Than One Table In The Same Scope

Doc_Duke — Tue, 11 Dec 2012 15:16:41 GMT

See

Re: Column Found In More Than One Table In The Same Scope

RichB — Tue, 11 Dec 2012 15:25:16 GMT

Thanks for the quick response.

I'm not sure how the link relates to my issue exactly. I'm fine with duplicate records pulling through (I don't believe they should but I can deal with that issue separately), my issue is that the query won't run at all, I simply get the error message above (as seen in the attached log).

I'd more than happily go and look at the code again but my lack of SAS code knowledge is not letting me see how to fix my issue with the link supplied.

Thanks

Rich

Re: Column Found In More Than One Table In The Same Scope

Georgetastic — Thu, 11 Apr 2013 22:53:10 GMT

I recently got this error too on a rather complex set of joins (8 tables).

What fixed it for me was to reset the order of all the joins, especially the left outer joins.

Good luck! You can message me if you have more questions.

Re: Column Found In More Than One Table In The Same Scope

TomKari — Fri, 12 Apr 2013 15:21:23 GMT

I find the FROM clause a little overwhelming, but one thing I noticed is that after a long series of join, your code contains

"ON (t3.IntermRef = t8.INT_NEW_AG_NO),CORPDIST.QUOTES_ALL_TEMP_RICH t3 LEFT JOIN SMEQUOTE.SME_PROSPECTS t2"

so you're using CORPDIST.QUOTES_ALL_TEMP_RICH as T3 twice in the same query.

Could this be a problem?

Tom

Re: Column Found In More Than One Table In The Same Scope

FJSchorr2 — Fri, 25 Aug 2017 15:11:49 GMT

I had the same problem as original poster RichB and, like Georgetastic, solved it by changing the order of my table joins.

I had a complex query with 9 tables, all of which used left joins. In the query Builder I clicked the "Join Tables" button to open the Tables and Joins window. I moved the tables around on the screen to get a visualization of how the tables were joined. In my case I had one monthly data table at the center of the visualization. The other tables were lookup tables that provided descriptions for the data values in the monthly data table. (I needed left joins because the monthly data table might have new values not found in the lookup tables).

By first joining the monthly data tables to the lookup tables and THEN joining the lookup tables to secondary lookup tables, all the errors went away.

Hope this helps!