雪花云数据平台我想写一个有三个或更多条件的snowsql IFF语句，但是一直出错

Question

Select *,  IFF((TARGET_QUIKPLAN.FORMS)='XX6041','L-6041', 
IFF(TARGET_QUIKPLAN.FORMS)='XX6042','L-6042', TARGET_QUIKPLAN.FORMS) AS newform
From TARGET_QUIKPLAN;

给:

Error: SQL compilation error: error line 1 at position 58 not enough arguments 
for function [IFF(TARGET_QUIKPLAN.FORMS)], expected 3, got 1

Answer 1

问题似乎是关于括号的。这个应该可以工作:

Select *, IFF(TARGET_QUIKPLAN.FORMS='XX6041','L-6041', IFF(TARGET_QUIKPLAN.FORMS='XX6042','L-6042', TARGET_QUIKPLAN.FORMS)) AS newform From TARGET_QUIKPLAN;

Answer 2

如果您需要嵌套IFF，但仍然在表达式之间使用相同的条件，更干净的方法是使用DECODE函数:

按顺序比较选择表达式和每个搜索表达式。一旦搜索表达式与选择表达式匹配，就返回相应的结果表达式。

DECODE( <expr> , <search1> , <result1> [ , <search2> , <result2> ... ] [ , <default> ] )

SELECT *, 
DECODE(TARGET_QUIKPLAN.FORMS, 'XX6041', 'L-6041',
'XX6042', 'L-6042',
TARGET_QUIKPLAN.FORMS) AS newform
FROM TARGET_QUIKPLAN;

或CASE表达式:

SELECT *,
CASE TARGET_QUIKPLAN.FORMS WHEN 'XX6041' THEN 'L-6041'
WHEN 'XX6042' THEN 'L-6042'
ELSE TARGET_QUIKPLAN.FORMS END AS new
FROM TARGET_QUIKPLAN;