How to join two table with same variable name

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

How to join two table with same variable name

Hi! I want to join 2 tables with similar variable name "Insurer", the second table is actually derived from first table. When I join it using the command "

create table data_tt1_diff as

select *, ifa-ifa_sum as ifa_diff, bnk-bnk_sum as bnk_diff, dir-dir_sum as dir_diff, grp-grp_sum as grp_diff, Total-Total_sum as Total_diff

from data_sum_tt1_by_insurer, test2.Ana_ttl_01_001_1

where (Total-Total_sum) gt 1000 and insurer = 'TOT'

order by quartercode;

This error occured

ERROR: Ambiguous reference, column insurer is in more than one table

Where do I make the mistakes and how to solve it?


Accepted Solutions
Solution
‎08-13-2015 12:06 PM
Super User
Posts: 11,343

Re: How to join two table with same variable name

Anywhere you reference a variable that appears in more than one table you need to tell the program which table version you want either use TableName.Variable or a table alias. Since your table names are longish I would tend to use an Alias.

create table data_tt1_diff as

select *, ifa-ifa_sum as ifa_diff, bnk-bnk_sum as bnk_diff, dir-dir_sum as dir_diff, grp-grp_sum as grp_diff, Total-Total_sum as Total_diff

from data_sum_tt1_by_insurer as T1, test2.Ana_ttl_01_001_1 as T2

where (Total-Total_sum) gt 1000 and T1.insurer = 'TOT'

order by quartercode;

The "as T1" creates the alias (lazy programmers just use T1 but I find it a helpful reminder that is what I have done), Then use the alias to reference which version such as

T1.insurer

If you are attempting to align the two tables you likely want to use a JOIN  ON. As written every record in each table is matched with every record in the other.

You may want:

from data_sum_tt1_by_insurer as T1 join test2.Ana_ttl_01_001_1 as T2 on T1.insurer=T2.insurer

Other variables may also need to be explicitly mentioned by source table in other clauses.

View solution in original post


All Replies
Solution
‎08-13-2015 12:06 PM
Super User
Posts: 11,343

Re: How to join two table with same variable name

Anywhere you reference a variable that appears in more than one table you need to tell the program which table version you want either use TableName.Variable or a table alias. Since your table names are longish I would tend to use an Alias.

create table data_tt1_diff as

select *, ifa-ifa_sum as ifa_diff, bnk-bnk_sum as bnk_diff, dir-dir_sum as dir_diff, grp-grp_sum as grp_diff, Total-Total_sum as Total_diff

from data_sum_tt1_by_insurer as T1, test2.Ana_ttl_01_001_1 as T2

where (Total-Total_sum) gt 1000 and T1.insurer = 'TOT'

order by quartercode;

The "as T1" creates the alias (lazy programmers just use T1 but I find it a helpful reminder that is what I have done), Then use the alias to reference which version such as

T1.insurer

If you are attempting to align the two tables you likely want to use a JOIN  ON. As written every record in each table is matched with every record in the other.

You may want:

from data_sum_tt1_by_insurer as T1 join test2.Ana_ttl_01_001_1 as T2 on T1.insurer=T2.insurer

Other variables may also need to be explicitly mentioned by source table in other clauses.

Occasional Contributor
Posts: 16

Re: How to join two table with same variable name

But This occur instead

create table data_tt1_diff as

91   select *, ifa-ifa_sum as ifa_diff, bnk-bnk_sum as bnk_diff, dir-dir_sum as dir_diff,

91 ! grp-grp_sum as grp_diff, Total-Total_sum as Total_diff

92   from data_sum_tt1_by_insurer as T1 left join test2.Ana_ttl_01_001_1 as T2 on

92 ! T1.insurer=T2.insurer

93   where (Total-Total_sum) gt 1000 and T1.insurer = 'TOT'

94   order by quartercode;

WARNING: Variable insurer already exists on file WORK.DATA_TT1_DIFF.

NOTE: Table WORK.DATA_TT1_DIFF created, with 0 rows and 19 columns.

Super User
Posts: 11,343

Re: How to join two table with same variable name

When you have code: SELECT *

then it says to bring in all variables from all tables. Since both T1 and T2 have the variable INSURER SAS is reminding you that you in effect issued SELECT INSURER, INSURER.

The data should be correct. The formal work around, and very annoying as I am not primarily an SQL programmer is to explicitly list source table and variable on the SELECT clause: SELECT T1.INSURER, T1.TOTAL, ...., T2.TOTAL_SUM, T1.IFA, T2.IFA_SUM....

or which ever table contributes which variable. The necessity for this syntax in SQL in general is also why there is the alias construct to allow references without having to type longtablename.var1, evenlongertablename.var2 everywhere.

Occasional Contributor
Posts: 16

Re: How to join two table with same variable name

No problem already, thanks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 356 views
  • 0 likes
  • 2 in conversation