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

[Sergiy Radyakin <serjradyakin@gmail.com>]

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/