Clever Geek Handbook

R ODBC - Query a column name with spaces

I am trying to execute a request via R OBDC. But one column name takes place on it. Eg [Account No]

.

I am using this code to request:

esiid_ac <- sqlQuery(myconn, paste("
 SELECT * FROM CustomerUsage WHERE ((CustomerUsage.Account No ='", 12345, "')) ", sep=""),as.is=TRUE)

I am getting the following error:

[1] "42000 102 [Microsoft] [ODBC Driver 11 for SQL Server] [SQL Server] Incorrect syntax next to" No "." [2] "[RODBC] ERROR: Failed SQLExecDirect '\ n SELECT * FROM CustomerUsage WHERE ((CustomerUsage.Account No =' 678987 '))'

How to solve this?

Is it possible to read this table with the column index instead of the column names?

Thank.

+3


source to share


5 answers


Have you tried square brackets (they work for me when there are special characters in the column names)?



esiid_ac <- sqlQuery(myconn, paste(" SELECT * FROM CustomerUsage WHERE ((CustomerUsage.[Account No] ='", 12345, "')) ", sep=""),as.is=TRUE)
+2


source


After working with quotes a bit, this worked for me:



df <- sqlQuery(myconn, 'SELECT * FROM mytable WHERE "column name" =123', as.is=TRUE)
+2


source


Can you try to put a column name like [account #] and then try?

+1


source


You may try...

df <- sqlQuery(myconn, "SELECT * FROM mytab WHERE `crazy column name` =123", as.is=TRUE)
+1


source


You can use \"COL_NAME\"

instead COL_NAME

and use it the way you have always used it. For example:

esiid_ac <- sqlQuery(myconn, "SELECT * FROM CustomerUsage WHERE \"Account No\" = 12345")
0


source






More articles:


All Articles