Re: st: SQL problem: ID already defined

[Andrew Dyck <tempmail@andrewdyck.com>]

With the Northwind database, instead of selecting all the fields of
both tables, which seems to be causing the problems, you could specify
only the fields you want. For example:

local query = "SELECT a.OrderId, a.UnitPrice, b.CustomerId FROM
orderdetails as a INNER JOIN orders as b ON a.OrderID = b.OrderID"
odbc load, exec("`query'") dsn("Northwind") clear

this will load just the three fields specified. If there are many
fields that you want from one table but just a couple from the other
you could use something like:

local query2 = ""SELECT a.*, b.CustomerId FROM orderdetails as a INNER
JOIN orders as b ON a.OrderID = b.OrderID"
odbc load, exec("`query'") dsn("Northwind") clear

which will load everything from Order Details but just one column from
the orders table.

I wonder if the issue of loading fields with the same name from
joining tables is something that Stata Corp will need to deal with? I
cannot think of any way to avoid the issue off the top of my head.

Good luck!
Andrew

On Mon, Jun 6, 2011 at 1:07 PM, Friedrich Huebler <fhuebler@gmail.com> wrote:
> 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/
>

*
*   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/