求助编写基于RN_SCOPE分支逻辑的SELECT CASE WHEN语句

你可以通过在WHERE子句中结合CASE WHEN来实现所需的分支逻辑，最终SQL语句如下：

SELECT code, description 
FROM table 
WHERE filter1 = 'Style' 
  AND filter2 IN (
    CASE '$EDIT#RN_SCOPE$'
      WHEN 'Project' THEN '$EDIT#FI_ASSET_CODE$'
      WHEN 'Standard' THEN '$PROJECT#PROJECT_Standard$'
    END
  )
ORDER BY sortorder;

逻辑说明 #

• 当$EDIT#RN_SCOPE$取值为Project时，filter2会匹配$EDIT#FI_ASSET_CODE$对应的内容
• 当$EDIT#RN_SCOPE$取值为Standard时，filter2会匹配$PROJECT#PROJECT_Standard$对应的内容
• 始终保留filter1 = 'Style'的过滤条件，结果按sortorder排序

如果你的SQL环境支持更直观的逻辑组合，也可以使用以下等价写法：

SELECT code, description 
FROM table 
WHERE filter1 = 'Style' 
  AND (
    ('$EDIT#RN_SCOPE$' = 'Project' AND filter2 IN ('$EDIT#FI_ASSET_CODE$'))
    OR 
    ('$EDIT#RN_SCOPE$' = 'Standard' AND filter2 IN ('$PROJECT#PROJECT_Standard$'))
  )
ORDER BY sortorder;

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