SQL中按生效日期展示套餐价格及异常SQL修复方案

现存可直接导致运行失败的问题如下：

• CASE表达式结构不完整，缺少多个结束符END，语法无法通过校验
• 日期字面量使用非标准的月-日-年文本格式，不同数据库兼容性差极易解析失败
• 未新增上月价格变动对应的时间判断分支，无法适配新套餐价格
• 注意确认判断字段close_date_pst是否在数据源表XYZ中存在，若不存在需要替换为你计算得到的WIN_DATE_PST

修改后的完整代码 #

SELECT
  TO_DATE(DATEADD(hh, -7, JSON_EXTRACT_PATH_TEXT(PROPERTY_HS_DATE_ENTERED_904938, 'value'))) AS WIN_DATE_PST,
  TO_VARCHAR(JSON_EXTRACT_PATH_TEXT(PROPERTY_PRODUCT_PURCHASED, 'value')) AS PRODUCT_PURCHASED,
  CASE
    -- 2021年9月2日前的旧价格
    WHEN close_date_pst < '2021-09-02' THEN
      CASE
        WHEN PRODUCT_PURCHASED = 'Academy - Admissions' THEN 2997
        WHEN PRODUCT_PURCHASED = 'Academy - Regular' THEN 1497
        WHEN PRODUCT_PURCHASED IN ('Support Monthly', 'Information Monthly', 'Academy - Monthly', 'Consulting Monthly') THEN 152.52
      END
    -- 2021年9月2日到上月调价前的价格
    WHEN close_date_pst < '2024-XX-XX' -- 此处替换为实际的上月调价生效日期，格式为YYYY-MM-DD
      THEN
      CASE
        WHEN PRODUCT_PURCHASED = 'Academy - Admissions' THEN 3146.85
        WHEN PRODUCT_PURCHASED = 'Academy - Regular' THEN 2096.85
        WHEN PRODUCT_PURCHASED IN ('Support Monthly', 'Information Monthly', 'Academy - Monthly', 'Consulting Monthly') THEN 160.15
      END
    -- 上月调价后的新价格，此处替换为你的新套餐定价
    ELSE
      CASE
        WHEN PRODUCT_PURCHASED = 'Academy - Admissions' THEN 【新定价数值】
        WHEN PRODUCT_PURCHASED = 'Academy - Regular' THEN 【新定价数值】
        WHEN PRODUCT_PURCHASED IN ('Support Monthly', 'Information Monthly', 'Academy - Monthly', 'Consulting Monthly') THEN 【新定价数值】
      END
  END AS REVENUE
FROM XYZ

如果判断交易时间的字段确实是你计算出的WIN_DATE_PST，把代码中所有close_date_pst替换为WIN_DATE_PST即可正常运行。

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