Error: Column was found in more than one table in the same scope.

[Tanguy]

Dear all,

I am using EG to run SQL code. (see the code attached).

This code generate the following errors.

Does anyone know how to solve this? The code should work...The SQL seems really correct to me...

ERROR: Column ras_var_ind_filt.Name was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.Entity_ID was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.MFI_Code was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.Reported_Period was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.Country was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.Highest_Level_of_Consolidation was found in more than one table in the same scope.

ERROR: Column ras_var_ind_filt.Entity_Type was found in more than one table in the same scope.

ETC.

[Tanguy]

I could't find any answer to this error on previous questions posed on the Forum.

Maybe it is the number of join or the order of the join (See FROM/WHERE clause in the code attached above.

Thank you

[RW9 · Accepted solution]

Hi,

Sorry, I am not here to debug an SQL query of that size.  At a brief glimpse I can see a couple of pointers:

In the first query you refer to ras_var_ind_filt.Name, however in the from statement you put:

L1RAWDAT.RAS_VAR_IND_FILT AS t3,

Now the first part is the table, the as T3 is the alias.  If you specify an alias for the data table you should be using that consistently throughout the query, T3.Name, for instance.  

In much the same way in your second query - which is where I presume you have the error - you are referencing one table:

STAGING.CREDIT_RISK_MI AS t1;

But in your select statement you use T1, and RAS_VAR_IND_FILT.CACE0100 for example, where the alias doesn't exist and nor does the table.

As I said, I don't have time to debug that whole code file.  What I would suggest however is that you looko at the thing as a whole as a brief glance over it shows to me a lot of code that isn't doing anything.  You should be able to vastly simply the code byusing data step, and a summary procedure (means or summary), just as an example, the second query is basically just selecting all the data and assigning labels and formats which can be vastly simplified with lists in datastep:

data want;
  set have;
  format abc def ghi comma21. xyz: percent12. aa1--aa5 date9.;
  label...;
run;