在R中使用dbGetQuery加载SQL查询时出现语法错误求助

执行R的dbGetQuery()函数时抛出如下错误：

Error: Failed to prepare query: ERROR:  syntax error at or near "DSG09272"
LINE 259: where t.event_name in ('DSG09272', 'DSG10012', 'DSG10042','D...

这段SQL在DBeaver里能正常运行，但报错指向WHERE子句中t.event_name的IN条件部分，检查后未发现语法问题。尝试删除大量注释内容（怀疑代码过长导致），问题依然存在。相关SQL片段如下：

where c.acct_id in (
select acct_id 
from database t 
where t.event_name in ('DSG09272', 'DSG10012', 'DSG10042','DSG10132','DSG10152','DSG10272','DSG10292','DSG10312','DSG11022','DSG11082','DSG11102','DSG11122','DSG11152','DSG11232','DSG11252','DSG11282',              'DSG12012','DSG12042','DSG12092','DSG12132','DSG12232','DSG12292','DSG01073','DSG01103','DSG01123','DSG01163','DSG01193','DSG01213', 'DSG02012', 'DSG02013', 'DSG02113','DSG02213','DSG02263','DSG02283','DSG03043','DSG03063','DSG03093','DSG03113','DSG03193','DSG03213','DSG03243','DSG03273','DSG03313','DSG04083','DSG04133'
) and left(t.plan_event_name, 3) = 'DSF' and t.ticket_status = 'A' and t.comp_name = 'Not Comp'
group by 1)

可行的解决办法 #

• 避免直接在R字符串中拼接长SQL：把SQL语句保存为单独的.sql文件，用readLines("your_query.sql") %>% paste(collapse = " ")读取后再传入dbGetQuery()，能规避字符串转义和换行带来的解析问题。
• 用临时表替代长IN条件：IN子句包含过多值可能触发驱动限制，先创建临时表存入所有event_name，再用JOIN关联查询：

  -- 创建临时表并插入数据
  CREATE TEMP TABLE temp_events (event_name VARCHAR);
  INSERT INTO temp_events VALUES ('DSG09272'), ('DSG10012'), ('DSG10042'), ...; -- 所有event_name
  
  -- 改写原查询
  where c.acct_id in (
  select acct_id 
  from database t 
  inner join temp_events te on t.event_name = te.event_name
  where left(t.plan_event_name, 3) = 'DSF' 
    and t.ticket_status = 'A' 
    and t.comp_name = 'Not Comp'
  group by 1)
  

• 使用参数化查询：用glue::glue_sql()或DBI::sqlInterpolate()构建带参数的查询，自动处理语法转义：

  library(glue)
  # 定义所有event_name的向量
  event_names <- c('DSG09272', 'DSG10012', 'DSG10042', ...)
  
  # 构建参数化SQL
  sql_query <- glue_sql("
    where c.acct_id in (
    select acct_id 
    from database t 
    where t.event_name in ({event_names*}) 
      and left(t.plan_event_name, 3) = 'DSF' 
      and t.ticket_status = 'A' 
      and t.comp_name = 'Not Comp'
    group by 1)
  ", .con = your_db_connection)
  
  # 执行查询
  dbGetQuery(your_db_connection, sql_query)
  

• 检查SQL中的特殊字符：确认SQL语句中没有隐藏的不可见字符（比如全角空格、换行符），可以把SQL复制到文本编辑器（如Notepad++）开启显示所有字符功能排查。

内容的提问来源于stack exchange，提问作者Nithin