I am having a hard time writing and extracting data from SQL Server in RStudio. My database hierarchy is as follows:
- LDS
- LDS
- HIG
- table_needed
- HIG
- LDS
I’ve tried:
x
ch <- DBI::dbConnect(odbc::odbc(), "LDS")
q1<- sqlQuery(ch,("
SELECT
AP_NAME,AP_Dep
FROM [hig].[tb_pp_oz_CURR]
Where AP_Dep <> 'K'|'C';
")
also
ch<- DBI::dbConnect(odbc::odbc(), "LDS")
q1<- sqlQuery(ch,("
SELECT
AP_NAME,AP_Dep
FROM in_schema("HIG","tb_pp_oz_CURR")
Where AP_Dep <> 'K'|'C';
")
From what I’ve read, my problem is that my LDS Schema is set to default. Is there a way to default my LDS schema? If not, is there a certain code I can use?
Advertisement
Answer
Here’s what I typically do:
library(dplyr)
library(DBI)
con <- DBI::dbConnect(odbc::odbc(), "LDS")
Then you can write your SQL as you normally would constructing the path to your schema as you would writing native SQL.
my_query <- 'SELECT
AP_NAME
,AP_Dep
FROM [LDS].[LDS].[HIG].[tb_pp_oz_CURR]
Where AP_Dep <> 'K'|'C''
Then you could pass that to your connections with:
returned_data <- tbl(con, sql(my_query)) %>% collect()
The other option is that you use dbplyr
and do something like
library(dbplyr)
returned_data <- tbl(con, in_schema("HIG", "tb_pp_oz_CURR")) %>%
filter(!AP_Dep %in% c("K", "C")) %>%
select(AP_NAME, AP_Dept) %>%
collect()