BookmarkSubscribeRSS Feed
RichB
Calcite | Level 5

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

5 REPLIES 5
RichB
Calcite | Level 5

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

Georgetastic
Calcite | Level 5

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.

FJSchorr2
Calcite | Level 5
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!
TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9386 views
  • 1 like
  • 5 in conversation