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

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/