R将必需的变量从ODBC/HANA连接传递到sql语句 [英] R Pass required variable from ODBC/HANA connection to sql statement

问题描述

我有一个要使用常用方法调用的表

I have a table I am trying to call with my usual method

sql <- 'SELECT TOP 10 *
         FROM "_SYS_BIC"."data-path.self-service.DOIP/table_name"'

df <- dbGetQuery(jdbcConnection, sql)

并收到错误

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT TOP 10 *
         FROM "_SYS_BIC"."data-path.self-service.DOIP/table_name" (SAP DBTech JDBC: [2048]: column store error: search table error:  [34023] Instantiation of calculation model failed;exception 306106: Undefined variable: $$IP_ExtractionWeekFrom$$. Variable is marked as required but not set in the query)

我一直试图将IP_ExtractionWeekFrom插入带有不带运气的where子句的sql语句中

I've been trying to insert IP_ExtractionWeekFrom into the sql statement with a where clause with no luck

param1 <- 201943

sql <- 'SELECT TOP 10 *
         FROM "_SYS_BIC"."ccf-edw.self-service.DOIP/R_CA_B_DemandPlan" where
         "$$IP_ExtractionWeek$$" = ?'

SpringVisit <- dbGetQuery(jdbcConnection, sql, param1)

我已经尝试过用"$$"和不使用，以及是否有"$$"都用引号引起来的术语.通常会遇到无效的列名"错误.

I've tried the term surrounded by the "$$" and without, and both with and without "$$" sourrounded in quotes and not. Usually am met with an "invalid column name" error.

是否应该使用where子句以外的名称来调用它?

Is this supposed to be called with something other than a where clause?

推荐答案

考虑使用R中的参数集成来维护工作的Tableau查询，并正确处理标识符的双引号和文字的单引号.

Consider maintaining your working Tableau query with the integration of parameters in R with properly handling of double quotes for identifiers and single quotes for literals.

此外，旧的('PLACEHOLDER'= ('<varname>', <varvalue>))语法不支持参数化.

Additionally, parameterization is not supported with the old ('PLACEHOLDER'= ('<varname>', <varvalue>)) syntax.

相反，如中所述，如何从HANA占位符中逃脱sql注入使用PLACEHOLDER."<varname>" => ?语法.

param1 <- 201943

sql <- "SELECT TOP 10 * 
        FROM \"_SYS_BIC\".\"ccf-edw.self-service.DOIP/R_CA_B_DemandPlan\"( 
             PLACEHOLDER.\"$$IP_ExtractionWeekFrom$$\", ?), 
             PLACEHOLDER.\"$$IP_ExtractionWeekTo$$\",?) 
        )\"_SYS_BIC\".\"ccf-edw.self-service.DOIP/R_CA_B_DemandPlan\" 
        WHERE (1 <> 0)"

SpringVisit <- dbGetQuery(jdbcConnection, sql, param1, param1)

此外，如果您的JDBC已经连接到架构_SYS_BIC，请使用同义修饰符 ::作为原始查询，以引用程序包和计算视图:

Additionally, if your JDBC already connects to the schema_SYS_BIC, use the synonymous qualifier :: as original query in order to reference package and calculation view:

sql <- "SELECT TOP 10 * 
        FROM \"ccf-edw.self-service.DOIP::R_CA_B_DemandPlan\"( 
             PLACEHOLDER.\"$$IP_ExtractionWeekFrom$$\", ?), 
             PLACEHOLDER.\"$$IP_ExtractionWeekTo$$\", ? ) 
        )\"ccf-edw.self-service.DOIP::R_CA_B_DemandPlan\" 
        WHERE (1 <> 0)"

这篇关于R将必需的变量从ODBC/HANA连接传递到sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！