Excel Power Query中Oracle查询空参数处理报错求助

问题根源 #

1. 空单元格返回null值，直接用Text.From(null)会触发类型转换错误
2. 在SQL字符串里嵌套M语言的if-then-else语法，不符合Power Query的字符串拼接规则

修复方案 #

核心思路是先单独处理参数空值，再分步构建SQL条件，避免在长字符串里直接嵌套逻辑：

// 获取参数，先处理analysis的空值
analysis = Excel.CurrentWorkbook(){[Name="analysis"]}[Content]{0}[Column1],
product = Excel.CurrentWorkbook(){[Name="product"]}[Content]{0}[Column1],
startdate = Excel.CurrentWorkbook(){[Name="startdate"]}[Content]{0}[Column1],
enddate = Excel.CurrentWorkbook(){[Name="enddate"]}[Content]{0}[Column1],
sqlstartdate = Date.From(startdate),
sqlenddate = Date.From(enddate),

// 将analysis的null转为空字符串，避免Text.From报错
analysisText = if analysis = null then "" else Text.From(analysis),

// 构建WHERE子句的条件列表
baseConditions = "PRODUCTID='" & Text.From(product) & "' AND COLLECTIONDT between TO_DATE('" & Text.From(sqlstartdate) & "','DD.MM.YYYY') AND TO_DATE('" & Text.From(sqlenddate) & "','DD.MM.YYYY')",
analysisCondition = if analysisText <> "" then " AND PARAMID='" & analysisText & "'" else "",

// 拼接完整SQL
fullSql = "SELECT S_SAMPLE.COLLECTIONDT , S_SAMPLE.S_SAMPLEID , S_SAMPLE.PRODUCTID , S_SAMPLE.U_CORNNR , S_SAMPLE.U_REACTORNR , S_SAMPLE.U_ADDITIONALINFO , S_SAMPLE.U_MATERIALNUMBER , SDIDATAITEM.PARAMID , SDIDATAITEM.DISPLAYVALUE , SDIDATAITEM.DISPLAYUNITS
FROM xx.S_SAMPLE
LEFT JOIN xx.SDIDATAITEM ON S_SAMPLE.S_SAMPLEID = SDIDATAITEM.KEYID1
WHERE " & baseConditions & analysisCondition,

// 执行查询
Source = Odbc.Query("dsn=xxdatabase", fullSql)

关键修改点 #

• 新增analysisText变量，提前把null转为空字符串，彻底避免Text.From(null)的错误
• 拆分条件为baseConditions（必选条件）和analysisCondition（可选条件），逻辑更清晰
• 只有当analysisText非空时，才拼接PARAMID的过滤条件，空值时自动忽略该条件

额外优化建议 #

如果担心SQL注入风险（虽然内部使用场景概率低），可以改用参数化查询，无需手动拼接字符串，Power Query会自动处理类型转换和空值：

// 参数化版本，更安全
Source = Odbc.Query("dsn=xxdatabase", 
    "SELECT S_SAMPLE.COLLECTIONDT , S_SAMPLE.S_SAMPLEID , S_SAMPLE.PRODUCTID , S_SAMPLE.U_CORNNR , S_SAMPLE.U_REACTORNR , S_SAMPLE.U_ADDITIONALINFO , S_SAMPLE.U_MATERIALNUMBER , SDIDATAITEM.PARAMID , SDIDATAITEM.DISPLAYVALUE , SDIDATAITEM.DISPLAYUNITS
    FROM xx.S_SAMPLE
    LEFT JOIN xx.SDIDATAITEM ON S_SAMPLE.S_SAMPLEID = SDIDATAITEM.KEYID1
    WHERE PRODUCTID=? AND COLLECTIONDT between ? AND ?" & if analysis <> null then " AND PARAMID=?" else "",
    {product, sqlstartdate, sqlenddate} & if analysis <> null then {analysis} else {}
)

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