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.f...@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 <bartjoo...@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@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@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.
______________________________________________
R-help@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.
