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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Eric Fail-3
Dear R-list,

I'm queering a M$ Access database with the sqlQuery function from the RODBC library. As I cannot make a working example with a database here is an illustrative example,

library(RODBC)
mdbConnect<-odbcConnectAccess("S:/data/ ... /databse.mdb")
data <- sqlQuery(mdbConnect, "select id, DOB, V1, V2, ..., V1009, V1011, V1013 from someTable")

I want everything in the table (someTable), except 'V1010' and 'V1012,' but I can't figure out how to make a negative or reverse SQL select statement. I have a lot of someTables and I have two or three variables in each table that I do not want R to fetch,

Is there a way to define a reverse select in SQL? One would imagine it would look something like this,

data <- sqlQuery(mdbConnect, "deselect V1010, V1o12 from someTable")

Thanks,
Eric

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to de-select with sqlQuery from the RODBC library?

Bart Joosen
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
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to de-select with sqlQuery from the RODBC library?

Eric Fail-3
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 <[hidden email]> 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-from-the-RODBC-library-tp4511189p4511800.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to de-select with sqlQuery from the RODBC library?

Bart Joosen
Another way is to use VBA to create a table which contains all the table names and column names.
Then use select from this table to create your query.

I didn't find any other solutions

Bart
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to de-select with sqlQuery from the RODBC library?

MacQueen, Don
In reply to this post by Eric Fail-3
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" <[hidden email]> 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 <[hidden email]>
>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.
>>
>> ______________________________________________
>> [hidden email] 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]]
>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.