HANA SQL的DO BEGIN...END语句中使用CASE WHEN出现语法错误咨询

根因 #

SAP HANA的SQLScript解析逻辑中，在DO BEGIN ... END匿名块内查询表变量时，CASE WHEN表达式中的字段如果没有明确绑定所属的表/表变量别名，解析器无法完成字段识别，会触发257语法错误。

可行解决方案 #

方案1：给表变量加别名，CASE字段绑定别名 #

给查询的表变量声明别名，CASE WHEN中引用的所有字段都带上别名即可正常执行：

DO BEGIN
    something_to_query_on = SELECT 1 AS first_value, 2 AS second_value FROM dummy
                            UNION
                            SELECT 4 AS first_value, 3 AS second_value FROM dummy
                            ;
    
    SELECT 
        t.first_value, 
        t.second_value,
        CASE WHEN t.first_value > t.second_value THEN 1 ELSE 0 END AS first_greater_second
    FROM :something_to_query_on AS t
    ;
END

方案2：提前在表变量中预计算目标字段 #

在给表变量赋值的阶段就完成CASE WHEN逻辑计算，后续查询直接读取预生成的字段即可：

DO BEGIN
    something_to_query_on = SELECT 
                                first_value,
                                second_value,
                                CASE WHEN first_value > second_value THEN 1 ELSE 0 END AS first_greater_second
                            FROM (
                                SELECT 1 AS first_value, 2 AS second_value FROM dummy
                                UNION
                                SELECT 4 AS first_value, 3 AS second_value FROM dummy
                            );
    
    SELECT first_value, second_value, first_greater_second
    FROM :something_to_query_on
    ;
END

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