Re: st: SQL problem: ID already defined

[Friedrich Huebler <fhuebler@gmail.com>]

Thank you, Andrew. I followed your suggestion and can now merge the
data from the two tables with a single SELECT command.

Friedrich

On Mon, Jun 6, 2011 at 6:18 PM, Andrew Dyck <tempmail@andrewdyck.com> wrote:
> 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/