Re: st: Re: odbc load from SQL if ID is in my Stata master dataset?

[Jen Zhen <jenzhen99@gmail.com>]

Great, it's all working now, thank you for the suggestions and the
detailed explanations!
JZ

On Wed, Sep 19, 2012 at 2:59 AM, Joseph Coveney <jcoveney@bigplanet.com> wrote:
> Jen Zhen wrote:
>
>> When I use the statement - SELECT * FROM table JOIN idlist ON
>> table.id=idlist.id -
>> I get the error "Column 'id' in field list is ambiguous, presumably
>> because the same id is contained more than once in my main table.
>> It's like when in a Stata merge some values of the id variable are
>> contained more than once in the master dataset and I want to merge in
>> the information from the using dataset for each of them. Not sure if
>> the JOIN command has an appropriate "option" for that?
>
> Yes, it does have one.  Specify taking only those columns from the first table,
> as follows:
>
>     SELECT A.* FROM table AS A INNER JOIN idlist AS B ON A.id = B.id
>
> This will prevent the second table's id from appearing in the selection and
> causing the ambiguity error message.
>
>> The second option I frankly don't fully understand.
>> Assuming the string length allowed in SQL is at least as high as that
>> in Stata, i.e. 244 characters, I think I could place all ID numbers
>> into about 4 stringths and then subsequently place these 4 stringths
>> into those IN(string) brackets.
>
> The 244-character limit on string lengths applies to Stata dataset variables and
> Stata scalars.  It does not apply to local macro variables or global macro
> variables.  Local macros and global macros can hold string lengths of about
> 32,000 characters.  That is why I built the SQL statement using a local macro
> variable.  So, there won't be a problem if you have only hundreds or a few
> thousand ID numbers.
>
>> However, I'm not sure how I could take all IDs contained in a Stata
>> dataset column and put them into a string?
>> I guess I could do so somehow manually by copy-pasting it into MS Word
>> and then copy-pasting back into the do file, but that doesn't look
>> very elegant or automatizable...
>
> That's what the following does.  It takes all IDs in the dataset and pastes each
> of them (and a comma) into the SQL statement during each passage through the
> loop.  (The last dataset observation's ID is pasted with a close parenthesis
> instead of a comma.)
>
>> >
>> >     local SQL_statement SELECT * FROM MyTable WHERE ID IN (
>> >     drop if missing(ID)
>         contract ID, freq(NoNo) // Just to be safe
>> >     quietly count
>> >     forvalues row = 1/`=`r(N)'-1' {
>> >         local SQL_statement `SQL_statement' `=ID[`row']',
>> >     }
>> >     local SQL_statement `SQL_statement' `=ID[`r(N)']');
>> >     odbc load, exec("`SQL_statement'") . . .
>
>
>
> *
> *   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/