已完成操作 #

• 通过RStudio的「Connections」标签页连接数据库；
• 使用SQL编辑器执行查询，成功预览结果：

-- !preview conn=con
SELECT xdailyissues.issue_date, xdailyissues.drugfull, xdailyissues.quantity_container, 
       xdailyissues.quantity_doseunits, xdailyissues.issue_type, xdailyissues.patient_hospitalno, 
       xdailyissues.costcentre, xdailyissues.lnkdid
FROM %PARALLEL JAC_Super.xdailyissues xdailyissues
WHERE (xdailyissues.issue_date >= {d '2025-04-01'} AND xdailyissues.issue_date <= {d '2025-04-30'})

• SQL结果标签页显示查询输出正确，但无法将结果转入R变量。

失败的尝试 #

尝试了多种方法，例如：

query <- "SELECT * FROM %PARALLEL JAC_Super.xdailyissues xdailyissues WHERE (xdailyissues.issue_date >= '2025-04-01' AND xdailyissues.issue_date <= '2025-04-30')"
issues <- dbGetQuery(con, query)

但持续收到ODBC错误，错误信息如下：

Error in `dbGetQuery()`:
! ODBC failed with error HY000 from [Iris ODBC][State : HY000][Native Code 400].
✖ 
• [C:\Program Files\RStudio\resources\app\bin\rsession-utf8.exe]
• [SQLCODE: <-400>:<Fatal error occurred>]
• [Error: <<UNDEFINED>Compile+17^%SYS.SQLSRV *%qinfo("type")>]
• [Location: <Prepare>]
• <SQL> '-- !preview conn=con SELECT "xdailyissues"."issue_date", "xdailyissues"."drugfull",
  "xdailyissues"."quantity_container", "xdailyissues"."quantity_doseunits", "xdailyissues"."issue_type",
  "xdailyissues"."patient_hospitalno", "xdailyissues"."costcentre", "xdailyissues"."lnkdid" FROM %PARALLEL
  "JAC_Super"."xdailyissues" "xdailyissues" WHERE ("xdailyissues"."issue_date">={d '2025-04-01'} AND
  "xdailyissues"."issue_date"<={d '2025-04-30'}) '
ℹ From nanodbc/nanodbc.cpp:1726.
Run `rlang::last_trace()` to see where the error occurred.

> query <- "SELECT xdailyissues.issue_date, xdailyissues.drugfull, xdailyissues.quantity_container, xdailyissues.quantity_doseunits, xdailyissues.issue_type, xdailyissues.patient_hospitalno, xdailyissues.costcentre, xdailyissues.lnkdid
+ FROM %PARALLEL JAC_Super.xdailyissues xdailyissues
+ WHERE (xdailyissues.issue_date >= {d '2025-04-01'} AND xdailyissues.issue_date <= {d '2025-04-30'})"
> issues <- dbGetQuery(con, query)
Error: nanodbc/nanodbc.cpp:2856:  2201
[Iris ODBC][State :  22018 ][Native Code 22005]
[C:\Program Files\RStudio\resources\app\bin\rsession-utf8.exe]
Error in assignment 
Warning message:
In dbClearResult(rs) : Result already cleared

> query <- "SELECT * FROM %PARALLEL JAC_Super.xdailyissues xdailyissues WHERE (CAST(xdailyissues.issue_date AS DATE) >= '2025-04-01' AND CAST(xdailyissues.issue_date AS DATE) <= '2025-04-30')"
> issues <- dbGetQuery(con, query)
Error in `dbGetQuery()`:
! ODBC failed with error from .
✖ 
• <SQL> 'SELECT * FROM %PARALLEL JAC_Super.xdailyissues xdailyissues WHERE (CAST(xdailyissues.issue_date AS DATE) >=
  '2025-04-01' AND CAST(xdailyissues.issue_date AS DATE) <= '2025-04-30')'
ℹ From nanodbc/nanodbc.cpp:1726.
Run `rlang::last_trace()` to see where the error occurred.

注：之前曾用以下代码成功拉取过表，但后续尝试无法重复，出现上述错误：

query <- "SELECT \"xdailyissues\".\"issue_date\", \"xdailyissues\".\"drugfull\", \"xdailyissues\".\"quantity_container\", \"xdailyissues\".\"quantity_doseunits\", \"xdailyissues\".\"issue_type\", \"xdailyissues\".\"patient_hospitalno\", \"xdailyissues\".\"costcentre\", \"xdailyissues\".\"lnkdid\"
  FROM %PARALLEL \"JAC_Super\".\"xdailyissues\" \"xdailyissues\"
  WHERE (\"xdailyissues\".\"issue_date\" >= {d '2024-04-01'} AND \"xdailyissues\".\"issue_date\" <= {d '2025-03-31'}) 
  AND (\"xdailyissues\".\"costcentre\" LIKE 'Q4A%' OR \"xdailyissues\".\"costcentre\" LIKE 'Q4C%' OR \"xdailyissues\".\"costcentre\" LIKE 'Q4D%') 
  AND (\"xdailyissues\".\"drugfull\" LIKE 'Zopiclone%' OR \"xdailyissues\".\"drugfull\" LIKE 'Zolpidem%')
"

问题 #

如何将已在SQL结果标签页获取的SQL查询结果导入R数据框，且无需手动重新执行查询？希望避免不必要的外部数据刷新，尤其是考虑到Power BI的不可预测行为。

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