RE: st: Using OBDC to reference a Stata file?

[<stata@spandrel.net>]

Thanks. You are roughly correct,  though in fact my first (and second)
queries already consiste of stacked sets of JOIN statements in order to
construct the different lists. I have tried combining the two, but it
becomes almost impossible to read/debug, so my hope was to create one list
and then refer to it when creating the second.  

Cheers,
J





-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Sergiy Radyakin
Sent: Monday, September 30, 2013 6:01 PM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: Using OBDC to reference a Stata file?

Jeph, suppose your original mySQL is something like:

SELECT *
FROM Customers c
WHERE c.State = 'NY'

and now you want to get their orders. Then just do the inner join with the
same condition in -where-.

SELECT *
FROM Customers c
INNER JOIN CustomerOrders co
    ON co.CustomerID = c.CustomerID
WHERE c.State = 'NY'

There are two (at least) downsides:
1. you can not pre-filter the list of ids in Stata (after your first query).
You have to put everything in SQL, and 2. this will be a second query to the
database, which potentially might produce more records than the original
query (if the DB was updated in the meanwhile).

Best, Sergiy Radyakin


On Mon, Sep 30, 2013 at 4:30 PM,  <stata@spandrel.net> wrote:
> I'm trying to use Stata to construct tables from a very large database 
> (ie,
> terrabytes) using ODBC.  I'm new to SQL, but because of the size of 
> the database am trying to do as much as I can via the SQL requests to 
> avoid working with very large files.
>
> Currently, I am able to create, say, table1.dta which is a list of 
> identifiers that meet a certain criteria; this results from a very 
> complex SQL statement that I submit via
>
> . odbc, exec(`mySQL')
> . save table1
>
> Now I would like to use table1 in my next SQL query so I select only 
> those identifiers. Possible?
>
> It seems not, but I feel compelled to ask because the advice I have 
> received from those who routinely use this database is to use SAS, 
> which will in fact let me reference SAS datasets in an SQL procedure. I'd
prefer not to.
>
> Thanks,
> Jeph
>
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/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/faqs/resources/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/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/