How to "translate" SQL query to R syntax

I know how to get odbc connection

odbcChannel <- odbcDriverConnect("DRIVER={driver name};SERVER=server name;Database=db name;

But I don't know how to refer table and columns from that connection. Now I need fetching columns from 3 tables from that server and database , how can I "translate" SQL query to meet R syntax below ?

Set @id = 1234
create table abc as
select run.run_id as 'ID', run.qtr_ind as 'Quarter', vdr.pro_id, prod.prod_name
from DBAPM01.dbo.t_vendor as vdr,
DBAPM01.dbo.t_product as prod,
DBAPM01.dbo.t_qtr_run as run
vdr.run_id = @id and
prod.prod_id = vdr.prod_id and
run.run_id = vdr.run_id
group by vdr.prod_id, prod.prod_name, run.qtr_ind
order by prod.prod_name

I have only used R with databases a few times, so treat my suggestions with caution.
Queries are passed to the database as strings. For example, once you have a connection named con

QueryString = "SELECT ID, Name, Address FROM TABLE1 WHERE ID = 1"
AllDat <- dbGetQuery(con, QueryString)

dbGetQuery is from the DBI package. AllDat will be a data frame of the query results.

1 Like

Have a look at dbplyr as well. dplyr code is similar to SQL.

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.