Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down at the end of May, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: SQL problem: ID already defined


From   Friedrich Huebler <fhuebler@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   st: SQL problem: ID already defined
Date   Mon, 6 Jun 2011 16:07:37 -0400

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/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index