Using where clause for R Variable in R script to use it in SQL statement

Question

I have two tables; viz table1 = PID (primary key) + 20 other columns & 200 records from database1 AND table2 = [Serial no] (primary key) + 10 other columns & 300 records from database2.

I am trying to extract values from table2 where PID = [Serial no].

Note: PID = [SCK34ICV7, NSCK876DL, ......].

I refered "Pass string Variable in R script to use it in SQL statement"

t1 <- sqlquery(db1, "select * from table1")
r1 <- t1$PID
class(r1) = 'factor'
t2 <- sqlquery(db2, "select * from table2 where [Serial no] = '(",r1,")' ",sep ="")

I also tried other functions viz paste0(), fn$ from gsubfn and sprintf() and getting error like - 'c is not a recognized built in function name' ; 'Incorrect syntax'.

Please suggest the best way to do it.

Reg,

Mrutyunjaya

Florian · Accepted Answer · 2017-08-07 10:38:23Z

3

Your query is off. See here for what the proper format should be.

r1 <- c("PID1","PID2","PID3")

wrong

paste("select * from table2 where [Serial no] = '(",r1,")' ",sep ="")

output:

[1] "select * from table2 where [Serial no] = '(PID1)' " "select * from table2 where [Serial no] = '(PID2)' " "select * from table2 where [Serial no] = '(PID3)' "

correct

paste("select * from table2 where [Serial no] IN (",paste(r1,collapse=", "),") ",sep ="")

Output:

[1] "select * from table2 where [Serial no] IN (PID1, PID2, PID3) "

So the query becomes:

t2 <- sqlquery(db2,paste0("select * from table2 where [Serial no] IN (",paste(r1,collapse=", "),") ",sep =""))

Hope this helps.

answered Aug 7, 2017 at 10:38

Florian

25.6k5 gold badges56 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jayahe Over a year ago

@ Florian, Getting error - 42S22 207 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'PID1'. What does this mean.

Florian Over a year ago

You should not use my values for r1, I created some dummy data with the line r1 <- c("PID1","PID2","PID3") for my example. You should use your own statement for that.

Jayahe Over a year ago

I used my own vector 'r1'. r1 <- t1$PID

Jayahe Over a year ago

Nope. FYI, str(r1) : Factor w/ 300 levels "SCK34ICV7",..: 334 11465 21762 14917 20282 20927 15958 3514 7430 7207 ...

Florian Over a year ago

Well, there has to be the word 'PID1' somewhere in your code? Try CTRL+F and type in PID1.

|

Collectives™ on Stack Overflow

Using where clause for R Variable in R script to use it in SQL statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related