0

I want to use dbplyr to define a hand-written SQL query rather than a table. How can I do that?

For example, I would like to do something like this:

my_custom_sql_tbl <- tbl( con, "SELECT * FROM MY_DATABASE_TABLE WHERE FOO IS NULL" )

my_custom_sql_tbl %>% select( my_columns )

The variable my_custom_sql_tbl would now act as a tbl(...) in R, but it would have custom SQL code running underneath. Obviously, I intend to have more interesting/complicated SQL than the things I could easily do in dbplyr.

I tried the above, but it didn't work. It returns an error of "table or view does not exist" or "identifier is too long".

Any suggestions?

Kalin
  • 1,691
  • 2
  • 16
  • 22
  • Since `dbplyr` [already imports `DBI`](https://cran.r-project.org/web/packages/dbplyr/index.html), you can just do `my_tbl <- DBI::dbGetQuery(con, "SELECT ... NULL")`. There is no need in `dbplyr` to necessarily replicate this ... though this means you are subject to the specific SQL dialect based on the server you are using, something `dbplyr` aims to insulate you from. – r2evans Nov 09 '18 at 22:36
  • 2
    The example on `?src_dbi` suggests `query <- sql("SELECT * FROM mtcars LIMIT 3"); tbl(src, query)` but I'm doing no more than reading a man page so not very definitive... – Martin Morgan Nov 09 '18 at 22:46
  • @MartinMorgan, thank you. I'm sorry, I didn't really know about how the different DB sources get set up and used (for example, I had no idea that I should look to `src_dbi`...). But anyway, *thank you*: this is exactly what I was looking for. Care to grab some points by posting as an answer ? :-) – Kalin Jan 22 '19 at 23:48

0 Answers0