R passing SQL results to sql statement

I am pulling some results from MySQL database like below:

GetJobCodes=paste0("select EMPLID from jobCurrent where JOBCODE='",JOBCODE,"'")
JOBCODES = dbGetQuery(connection,GetJobCodes)

and I want to pass above JOBCODES results to other SQL statement

statement=sprintf("SELECT A.EMPLID, A.CLASS_ID FROM lmsEnroll A JOIN lmsCourses 
              B ON A.COURSE_ID=B.COURSE_ID AND B.REQUIRED=0 WHERE A.EMPLID IN (%s)",JOBCODES)

But when I am passing to above statement it is print like

SELECT A.EMPLID, A.CLASS_ID FROM lmsEnroll A JOIN lmsCourses 
       B ON A.COURSE_ID=B.COURSE_ID AND B.REQUIRED=0 WHERE A.EMPLID IN "C("00330022","00033322")")

which is not correct, I want to print them like :

SELECT A.EMPLID, A.CLASS_ID FROM lmsEnroll A JOIN lmsCourses 
       B ON A.COURSE_ID=B.COURSE_ID AND B.REQUIRED=0 WHERE A.EMPLID IN ("00330022","00033322")

I have used ShQuote function, but it is not help. I appreciate if anyone could help me.

Answer

Got it! This issue all about rendering thing. By selecting EMPLID with JOBCODES dataframe got issue resolved .

JOBCODES =paste(shQuote(JOBCODES$EMPLID,type="sh"),collapse=",") //Selecting EMPLID column

statement=paste0("SELECT A.EMPLID, A.CLASS_ID FROM lmsEnroll A JOIN lmsCourses B ON A.COURSE_ID=B.COURSE_ID AND B.REQUIRED=0 WHERE A.EMPLID IN 
(",JOBCODES,")")

Advertisement

Answer