Desktop productivity for business analysts and programmers

Column Found In More Than One Table In The Same Scope

Reply
New Contributor
Posts: 2

Column Found In More Than One Table In The Same Scope

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

Attachment
Trusted Advisor
Posts: 2,114

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

See

New Contributor
Posts: 2

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

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

New Contributor
Posts: 2

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

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.

New User
Posts: 1

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

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!
PROC Star
Posts: 1,143

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 2479 views
  • 0 likes
  • 5 in conversation