[R] Is it possible to de-select with sqlQuery from the RODBC library?

[MacQueen, Don]

I've never heard of a an SQL de-select, but if there is such a thing it
shouldn't be too hard to find via some web searches.

In the meantime, I would probably just do a select * to get all the fields
from the database tables, and then drop the unwanted ones afterwards in R.
I think this will give you simpler code, thus easier to understand and
check. And, unless the tables are huge, I doubt you'll see any performance
problem.

One way would be:

data <- sqlQuery(mdbConnect, "select * from someTable")
data <- subset(data, select=-c(V1010,V1o12))


Or, given your list of fields to exclude, something like the following
(untested) should work.
(hopefully my stupid email client won't shorten any of the lines of code)

q.lookup <- list(Table3 = c('V1010', 'V1012'),
   Table7 = c('V1040', 'V1052'),
   Table9 = 'ALL')

mydat <- q.lookup

for (nm in names(q.lookup)) {
  sql <- paste('select * from',nm)
  mydat[[nm]] <- sqlQuery(mdbConnect, sql)
  mydat[[nm]] <- mydat[[nm]][ , setdiff(names(mydat[[nm]]), q.lookup[[nm]]
]
}  

You now have a list; each element contains one of your tables.


Here is an example to show you what setdiff() is doing:
  setdiff(letters[1:10], c('b','d'))

It's not necessary to test for 'ALL' in that loop, provided none of your
input tables have a field named 'ALL'.
See:
   setdiff(letters[1:10], 'ALL')



And note: it's best to not use 'data' for the name of a data frame; it is
the name of an R-suppled function.



-Don

-- 
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062





On 3/28/12 11:17 AM, "Eric Fail" <eric.fail at gmx.us> wrote:

>Thank you Bart for your idea, the thing is that I have a large number of
>tables and I would like to avoid having to pull them at all.
>
>I currently have a list that I use as a lookup table in a loop with an if
>else statement to sort between tables I want to sqlFetch (take everything)
>and tables where I sqlQuery (only want part of the table). The names of
>the
>list itself constitute a positive definition of what tables I want to
>pull.
>
>Here in a reduced illustrative example of what I am doing. My problem is
>still that I would like to make negative selection so I get everything
>except 'V1010' and 'V1012' in table 3, and so forth (please see below).
>
>######   illustrative R example   ######
>
>q.lookup <- list(Table3 =   c('V1010', 'V1012'),
>                      Table7 =   c('V1040', 'V1052'),
>                      Table9 =   'ALL')
>dfn <- list()
>
>for(i in names(q.lookup)) {
>  if (q.lookup[[i]][1]=="ALL") {
>     query <- names(q.lookup[1])
>     table.n <- sqlFetch(mdbConnect, query)
>  } else if (q.lookup[[i]][1]!="ALL") {
>     query <- paste("select", paste(q.lookup[[i]], collapse=", "), "from",
>names(q.lookup[i]))
>     table.n <- sqlQuery(mdbConnect, query)
>  } else print("your SQL call is gone haywire, fix it in line 193-204")
>  dfn[[i]] <- table.n
>}
>
>###   end of illustrative R example   ####
>
>I could use your solution, I think, but if at all possible I would prefer
>to figure out how to make a negative SQL statement (I still imagine that
>there is some reverse function of the SQL select statement somewhere out
>there).
>
>With hight hopes.
>
>Eric
>
>On Wed, Mar 28, 2012 at 2:24 AM, Bart Joosen <bartjoosen at hotmail.com>
>wrote:
>
>> What you can do: "SELECT top 1 * FROM your_table;"
>> Use this selection to find all your column names in R
>> then paste everything together without the names you don't want and then
>> run
>> your query.
>>
>> Bart
>>
>> --
>> View this message in context:
>> 
>>http://r.789695.n4.nabble.com/Is-it-possible-to-de-select-with-sqlQuery-f
>>rom-the-RODBC-library-tp4511189p4511800.html
>> Sent from the R help mailing list archive at Nabble.com.
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.