topic Re: ERROR: Ambiguous reference, column "col-name" is in more than one table. in SAS Programming

ERROR: Ambiguous reference, column "col-name" is in more than one table.

FelipeVerissim0 — Thu, 01 Jun 2023 14:02:02 GMT

i'm transcripting a T-SQL in SAS BASE language, and in a Stored Procedure i'm trying to create a table with proc sql and using a inner join in a created table named "tabelaC":

proc sql;

title 'TabelaC';

title2 'tabela T402CONT';

create table tabelaC as

select

nr_ctr,

nr_adl,

cd_emp,

cd_und,

sg_mod,

cd_cli,

qt_tit,

datepart(dt_ope) format=date9. as dt_ope,

vr_pcp

from STGCREDM.STG_T402CONT

order by cd_emp,cd_und,cd_cli,nr_ctr,nr_adl,sg_mod;

quit;

After this first step i'm trying to create a table "tabelaTIAB" with this following program:

proc sql;

title 'TabelaTIAB';

title2 'tabela T402TIAB';

create table tabelaTIAB as

select

nr_ctr,

datepart(dt_ven) format=date9. as dt_ven,

cd_cli,

nr_adl,

sg_mod

from STGCREDM.STG_T402TIAB as tiab

INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON

tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod

order by cd_cli,nr_ctr,nr_adl,sg_mod;

quit;

I'm getting this following ERROR: Ambiguous reference, column nr_ctr is in more than one table.

27         
28         proc sql;
29         	title 'TabelaTIAB';
30         	title2 'tabela T402TIAB';
31           create table tabelaTIAB as
32         	select
33         	 nr_ctr,
34         	 datepart(dt_ven) format=date9. as dt_ven,
35         	 cd_cli,
36         	 nr_adl,
37         	 sg_mod
38         
39         	from STGCREDM.STG_T402TIAB as tiab
40         	INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON
41         	tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod
42         	order by cd_cli,nr_ctr,nr_adl,sg_mod;
ERROR: Ambiguous reference, column nr_ctr is in more than one table.

Can you guys help me?

Re: ERROR: Ambiguous reference, column "col-name" is in more than one table.

PaigeMiller — Thu, 01 Jun 2023 14:07:28 GMT

You are using nr_ctr in the ORDER statement, you have to specify which from which data set nr_ctr is to be used for this ordering.

Also, from now on, please post code using monospaced (also called fixed-width) fonts, that makes it easier to read. Here in the SAS communities, code should be placed into the "Insert SAS Code" window which appears when you click on the "little running man" icon.

Re: ERROR: Ambiguous reference, column "col-name" is in more than one table.

sbxkoenk — Thu, 01 Jun 2023 14:08:51 GMT

Just make it a two-level-name (in your "order by" statement):

tiab.nr_ctr or C.nr_ctr

Koen

Re: ERROR: Ambiguous reference, column "col-name" is in more than one table.

FelipeVerissim0 — Thu, 01 Jun 2023 17:34:43 GMT

proc sql;
	title 'TabelaTIAB';
	title2 'tabela T402TIAB';
  create table tabelaTIAB as
	select 
	 tiab.nr_ctr,
	 datepart(tiab.dt_ven) format=date9. as dt_ven,
	 tiab.cd_cli,
	 tiab.nr_adl,
	 tiab.sg_mod

	from STGCREDM.STG_T402TIAB as tiab
	INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod from work.tabelac) AS C ON 
	tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.nr_adl and tiab.sg_mod = C.sg_mod
	order by cd_cli,nr_ctr,nr_adl,sg_mod;
quit;

worked for me just putting the two-level-name in the columns in the SELECT statement. Thanks!