long select clause in SQL

[tjcnnl1]

I have a very long select clause and separate with several by data categories.

run information 5

runinf <- "select asp_run_id as runid,asp_run_ind as runind,asp_eval_qtr as evalqtr,asp_rerun_id as rerunid,asp_rpt_qlf as qlf,"

data information 10

datainf <- "pm_id as pmsid,
hco_id as hcoid,
rfi_ind_id as measid,
st_bsn_str_cd as state,
rfi_msr_set_id as msrsetid,
rfi_msr_set_cd as msrsetcd,
rfi_subset_id as rfi_subset_id,
msr_rsk_adj as radj,
jc_mop_opt_typ_cd as measuse,
expc_rt_typ as expctyp,"

select1 <- paste0(runinf, datainf)

There are new line characters in select1. How to remove those to make a correct select statement and what is a good way to do this? Thanks!

[dvetsch75]

You could use gsub('\n', '', select1) to remove all newlines from a string, but the better question is this: Is that really what you want to do? I think most database interfaces that are implemented in R handle newline characters just fine, just as R does. For example, if you writeLines your string instead of printing it, it looks much better.

runinf <- "select asp_run_id as runid,asp_run_ind as runind,asp_eval_qtr as evalqtr,asp_rerun_id as rerunid,asp_rpt_qlf as qlf,"


datainf <- "pm_id as pmsid,
hco_id as hcoid,
rfi_ind_id as measid,
st_bsn_str_cd as state,
rfi_msr_set_id as msrsetid,
rfi_msr_set_cd as msrsetcd,
rfi_subset_id as rfi_subset_id,
msr_rsk_adj as radj,
jc_mop_opt_typ_cd as measuse,
expc_rt_typ as expctyp,"

select1 <- paste0(runinf, datainf)

writeLines(select1)
#> select asp_run_id as runid,asp_run_ind as runind,asp_eval_qtr as evalqtr,asp_rerun_id as rerunid,asp_rpt_qlf as qlf,pm_id as pmsid,
#> hco_id as hcoid,
#> rfi_ind_id as measid,
#> st_bsn_str_cd as state,
#> rfi_msr_set_id as msrsetid,
#> rfi_msr_set_cd as msrsetcd,
#> rfi_subset_id as rfi_subset_id,
#> msr_rsk_adj as radj,
#> jc_mop_opt_typ_cd as measuse,
#> expc_rt_typ as expctyp,

^{Created on 2022-06-20 by the reprex package (v1.0.0)}

Are you getting an issue when you pass select1 as a query to a database?

[tjcnnl1]

You are right. It isn't new line char issue. The stmt1 is not recognized by sqlQurey and d1 isn't a table and complains syntax error. The purpose are packing multiple columns in select clause and using a variable to filter rows and then extract a table from database - don't know why the codes don't work for me. Thank you.

runinf <- " 'select asp_run_id as runid,asp_run_ind as runind,asp_eval_qtr as evalqtr,asp_rerun_id as rerunid,asp_rpt_qlf as qlf,asp_rpt_tm_perd as month,asp_rpt_yr as year,"

datainf <- "pm_id as pmsid,
hco_id as hcoid,
rfi_ind_id as measid,
st_bsn_str_cd as state,
rfi_msr_set_id as msrsetid,
rfi_msr_set_cd as msrsetcd,
rfi_subset_id as rfi_subset_id,"

msrinf <- "asp_msr_rpt_id as meastype,
asp_drc_of_msr as direct,
msr_rsk_adj as radj,
jc_mop_opt_typ_cd as measuse,
expc_rt_typ as expctyp "

s1 <- paste0(runinf,datainf,msrinf)
writeLines(s1)
sql_id <- "'1388'"
stmt1 <- glue(s1,"from t_asp_yr_cmpr_chart where runid = {sql_id}; '")
writeLines(stmt1)

d1 <- sqlQuery(odbcChannel2, stmt1 , as.is = TRUE) %>% head(2)
str(d1)