st: SQL problem: ID already defined

[Friedrich Huebler <fhuebler@gmail.com>]

I am trying to load some data from an ODBC source into Stata with
-odbc load, exec()-. A command that works in SQL Server Management
Studio fails in Stata when the two variables that are used to match
observations have the same name in both tables. The problem can be
reproduced with the Northwind database that is usually installed with
MS Access.

. odbc query "Northwind"
. odbc load, exec("SELECT * FROM [Order Details] as a INNER JOIN
[Orders] as b ON a.OrderID = b.OrderID")
OrderID already defined
r(110);

My solution is to load the two tables separately, save them as Stata
datasets, and then merge them over the common identifier. Instead of
the commands below, is it possible to join the tables directly with a
variation of the SQL command shown above?

. odbc load, exec("SELECT * FROM [Order Details]")
. sort OrderID
. tempfile details
. save `details'
. clear
. odbc load, exec("SELECT * FROM [Orders]")
. sort OrderID
. merge 1:m OrderID using `details'

Thanks,

Friedrich
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/