I'm writing an R script to get some database data and then do stuff with it, using the RODBC package. Currently all my sqlQuery commands are one long string;
stsample<-sqlQuery(odcon, paste"select * from bob.DESIGNSAMPLE T1, bob.DESIGNSUBJECTGROUP T2, bob.DESIGNEVENT T3, bob.CONFIGSAMPLETYPES T4 WHERE T1.SUBJECTGROUPID = T2.SUBJECTGROUPID AND T1.TREATMENTEVENTID = T3.TREATMENTEVENTID AND T1.SAMPLETYPEKEY = T4.SAMPLETYPEKEY AND T1.STUDYID = T2.STUDYID AND T1.STUDYID = T3.STUDYID AND T1.STUDYID = ", chstudid, sep=""))
head(stsample)
which looks ugly and is hard to read/update. I've tried putting them multiline, but then new line characters get in the way, currently my best is this using lots of paste's;
stsample<-sqlQuery(odcon,
paste(
"select ",
"* ",
"from ",
"BOB.DESIGNSAMPLE T1, ",
"BOB.DESIGNSUBJECTGROUP T2, ",
"BOB.DESIGNEVENT T3, ",
"BOB.CONFIGSAMPLETYPES T4 ",
"WHERE ",
"T1.SUBJECTGROUPID = T2.SUBJECTGROUPID ",
"AND T1.TREATMENTEVENTID = T3.TREATMENTEVENTID ",
"AND T1.SAMPLETYPEKEY = T4.SAMPLETYPEKEY ",
"AND T1.STUDYID = T2.STUDYID ",
"AND T1.STUDYID = T3.STUDYID ",
"AND T1.STUDYID = ",chstudid,
sep="")
)
head(stsample)
But I don't like having to put quotes around everyline, and getting my whitespace correct. Is there a better way ?
I would use something like this:
stsample<-sqlQuery(odcon,
paste("
####DATASET CONSTRUCTION QUERY #########
select
*
from
BOB.DESIGNSAMPLE T1,
BOB.DESIGNSUBJECTGROUP T2,
BOB.DESIGNEVENT T3,
BOB.CONFIGSAMPLETYPES T4
WHERE
T1.SUBJECTGROUPID = T2.SUBJECTGROUPID
AND T1.TREATMENTEVENTID = T3.TREATMENTEVENTID
AND T1.SAMPLETYPEKEY = T4.SAMPLETYPEKEY
AND T1.STUDYID = T2.STUDYID
AND T1.STUDYID = T3.STUDYID
AND T1.STUDYID =
###################################
", as.character(chstudid), sep="")
)