SAP IDT中MSSQL查询验证通过但查看返回值报错如何解决

问题场景 #

在SAP Information Design Tool（IDT）对接MSSQL数据库场景下，编写的查询可通过IDT内置语法校验，但执行查询查看返回值时提示错误；同一段SQL在PowerBI中可正常运行并返回结果。

测试所用SQL语句 #

WITH c   AS
    (SELECT A.QuestionHdrKey AS QuestionHdrKey1,
            A.DivisionKey AS DivisionKey1,
            COUNT(1) AS QCount
     FROM Mobile.QuestionLocationMap A WITH (NOLOCK)
          INNER JOIN Mobile.Question b WITH (NOLOCK) ON A.QuestionKey = b.PKey
     WHERE A.QuestionHdrKey = 200305685377000000
     GROUP BY A.QuestionHdrKey,
              A.DivisionKey),
d   AS
    (SELECT a.QuestionHdrKey,
            a.QuestionKey,
            a.DivisionKey,
            a.InvDate,
            a.HdrKey,
            ROW_NUMBER() OVER (PARTITION BY a.DivisionKey,
                                            a.invdate,
                                            a.HdrKey
                               ORDER BY a.QuestionKey) AS RowId
     FROM mobile.StatusReport a WITH (NOLOCK)
          INNER JOIN mobile.Question b WITH (NOLOCK) ON a.QuestionKey = b.PKey
                                                    AND b.QuestionType = 'rate'
                                                    AND InputType = 'numeric'
     WHERE a.QuestionHdrKey = '200305685377000000'
     GROUP BY a.QuestionHdrKey,
              a.DivisionKey,
              a.HdrKey,
              a.InvDate,
              a.QuestionKey)
SELECT a.DivisionKey,
       a.InvDate AS ModifiedDate,
       a.QuestionHdrKey,
       a.HdrKey,
       COUNT(DISTINCT a.QuestionKey) AS QuestionKey,
       SUM(CAST(a.Value AS int)) AS value,
       SUM(b.Rate) AS RATE
--case when a.invdate between '2020-05-09' and '2022-03-31' then case when  then case when    cast(Value as int)*5>5 then 5 else cast(Value as int)*5  end else  cast(Value as int) end  as value,c.QCount

FROM mobile.StatusReport a WITH (NOLOCK)
     INNER JOIN mobile.Question b WITH (NOLOCK) ON a.QuestionKey = b.PKey
                                               AND b.QuestionType = 'rate'
                                               AND InputType = 'numeric'
     INNER JOIN c WITH (NOLOCK) ON a.DivisionKey = c.DivisionKey1
     INNER JOIN d WITH (NOLOCK) ON a.HdrKey = d.HdrKey
                               AND a.QuestionKey = d.QuestionKey
WHERE a.QuestionHdrKey = '200305685377000000'
  --and  a.HdrKey='210305757994230000'
  AND d.RowId <= c.QCount
GROUP BY a.DivisionKey,
         a.InvDate,
         a.QuestionHdrKey,
         a.HdrKey,
         c.QCount;

PowerBI中正常返回的结果示例 #

排查解决步骤 #

按优先级从高到低依次验证处理：

• 优先开启SQL直通模式：IDT内置的SQL解析器重写逻辑对MSSQL的CTE（WITH公共表表达式）存在已知兼容bug，语法校验仅做关键字匹配不会触发重写逻辑，实际执行时解析器错误拆分CTE语句就会报错。打开对应MSSQL关系连接的编辑页，在通用参数配置中勾选「原生SQL直通」选项，禁用IDT自带的SQL改写、重排逻辑，直接将SQL发送给MSSQL引擎执行即可。
• 清理SQL中的单行注释：IDT旧版本解析--开头的单行注释时存在截断bug，会把注释行之后的所有SQL内容判定为注释内容，导致实际执行的语句不完整。把SQL中注释掉的case逻辑、注释掉的HdrKey筛选条件全部删除后再测试。
• 统一字段匹配的参数类型：当前SQL中对QuestionHdrKey字段的筛选同时存在数值格式200305685377000000、字符串格式'200305685377000000'两种写法，PowerBI会自动做隐式类型转换，IDT的JDBC/ODBC驱动在结果集拉取阶段遇到类型不匹配会直接抛错。对照数据库中该字段的实际类型，统一所有筛选位置的传值格式。
• 转义保留字别名：SELECT子句中的value、RATE属于数据库和IDT的内置保留字，PowerBI会自动处理别名转义，IDT映射结果集字段时遇到未转义的保留字会报字段读取错误。给别名加MSSQL标准方括号转义，改写为SUM(CAST(a.Value AS int)) AS [value]、SUM(b.Rate) AS [RATE]、COUNT(DISTINCT a.QuestionKey) AS [QuestionKey]即可。
• 替换单表级别的NOLOCK提示：IDT解析器无法正确识别MSSQL专属的WITH (NOLOCK)表提示位置，SQL重写时容易把提示放到错误语法位置触发执行错误。可以临时删除所有NOLOCK提示测试，若需要保留READ UNCOMMITTED隔离级别，在SQL最开头加SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;代替单表NOLOCK写法即可。

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