T-SQL含XML处理的查询语句转换为HANA SQL求助

原T-SQL XML逻辑说明 #

你贴的XML处理逻辑本质是把IIS_EPC_PRO_SETTINGS表中ModSetting='HRUSer'对应的逗号分隔的SettingVal字段值，拆分为单个RoleID的行集，无需深究XML语法，HANA可直接通过字符串拆分函数实现等价效果，不需要引入XML处理逻辑。

转换后HANA SQL代码 #

版本1：HANA 2.0 SPS03及以上（支持STRING_SPLIT，推荐） #

SELECT 
    t0.empID AS "UserID", 
    t0.lastName || ', ' || t0.firstName AS "Employee"
FROM 
    dbo.OHEM t0
INNER JOIN dbo.HEM6 t1 
    ON t0.empid = t1.empID
INNER JOIN (
    SELECT TRIM(VALUE) AS "RoleID"
    FROM dbo.IIS_EPC_PRO_SETTINGS,
         STRING_SPLIT(SettingVal, ',')
    WHERE ModSetting = 'HRUSer'
) t2 
    ON t1.roleID = t2."RoleID"
WHERE 
    t0.Active = 'Y'
    AND NOT EXISTS (
        SELECT 1 
        FROM dbo.IIS_EPC_PRO_WFSESSIONS t3 
        WHERE t3.UserId = t0.empID
    )
GROUP BY 
    t0.empID, 
    t0.lastName, 
    t0.firstName
ORDER BY 
    t0.lastName, 
    t0.firstName;

版本2：低版本HANA兼容（不支持STRING_SPLIT） #

SELECT 
    t0.empID AS "UserID", 
    t0.lastName || ', ' || t0.firstName AS "Employee"
FROM 
    dbo.OHEM t0
INNER JOIN dbo.HEM6 t1 
    ON t0.empid = t1.empID
INNER JOIN (
    SELECT TRIM(SUBSTR(SettingVal, pos, LEAD(pos, 1, LENGTH(SettingVal)+1) OVER (ORDER BY pos) - pos)) AS "RoleID"
    FROM (
        SELECT 
            s.SettingVal,
            GENERATE_POSITION.pos
        FROM (
            SELECT SettingVal 
            FROM dbo.IIS_EPC_PRO_SETTINGS 
            WHERE ModSetting = 'HRUSer'
        ) s,
        SERIES_GENERATE_INTEGER(1, 1, LENGTH(s.SettingVal)) GENERATE_POSITION
        WHERE SUBSTR(s.SettingVal, GENERATE_POSITION.pos, 1) = ',' 
           OR GENERATE_POSITION.pos = 1
    )
) t2 
    ON t1.roleID = t2."RoleID"
WHERE 
    t0.Active = 'Y'
    AND NOT EXISTS (
        SELECT 1 
        FROM dbo.IIS_EPC_PRO_WFSESSIONS t3 
        WHERE t3.UserId = t0.empID
    )
GROUP BY 
    t0.empID, 
    t0.lastName, 
    t0.firstName
ORDER BY 
    t0.lastName, 
    t0.firstName;

注意事项 #

• 代码中保留了原逻辑的dbo schema，如果你的HANA实例中表对应schema不是dbo，需要替换为实际schema名称
• 内置的TRIM函数用于处理拆分后RoleID前后的多余空格，和原T-SQL逻辑效果一致，如果原SettingVal没有空格可以去掉

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