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 on April 23, and its replacement, statalist.org is already up and running.


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

Re: st: SQL problem: ID already defined


From   Andrew Dyck <tempmail@andrewdyck.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: SQL problem: ID already defined
Date   Mon, 6 Jun 2011 15:18:13 -0700

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/


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