请求协助将Qlikview代码转换为Snowflake SQL并解析ApplyMap用法

一、QlikView中ApplyMap的作用 #

• ApplyMap是QlikView的键值映射函数，核心作用是通过预定义的“键-值”映射表（由MAPPING LOAD创建），将输入的键匹配到对应的结果值；如果找不到匹配的键，就返回指定的默认值
• 语法：ApplyMap('映射表名称', 待匹配的键, 无匹配时的默认值)
• 原代码里的具体逻辑：
  • 当POD_SCHED='1'时，调用EXCEL_PO_STATUS_SCHEDULED映射表，传入当前行的PO_STATUS_KEY作为匹配键，无匹配则返回null
  • 当POD_SCHED不为'1'时，调用EXCEL_PO_STATUS_UNSCHEDULED映射表，传入当前行的POD_NBR作为匹配键，无匹配则返回null

二、原QlikView代码逻辑拆解 #

1. 生成两个映射表
   • EXCEL_PO_STATUS_SCHEDULED：从VW_POD_DET中提取唯一的拼接键（由POD_NBR、POD_LINE等字段用|拼接而成），根据POD_END_EFF##1是否为空，标记状态为open或closed
   • EXCEL_PO_STATUS_UNSCHEDULED：从VW_PO_MSTR中提取唯一的PO_NBR作为键，根据原表的PO_STAT是否为空，标记状态为open或closed
2. 更新主表字段
   • 对EXCEL_REQ_PO_MSTR做左自连接，根据POD_SCHED的值选择对应的映射表，生成PO_STAT_DESC字段并追加到主表

三、正确的Snowflake SQL转换 #

方式1：预创建映射视图（对应QlikView的映射表） #

先创建两个映射视图，对应原QlikView中的MAPPING LOAD逻辑：

-- 对应EXCEL_PO_STATUS_SCHEDULED映射表
CREATE OR REPLACE VIEW dev_marts.purchasing.EXCEL_PO_STATUS_SCHEDULED AS
SELECT DISTINCT 
    CONCAT_WS('|', POD_NBR, POD_LINE, POD_PART, POD_LOC, POD_PO_SITE, POD_DUE_DATE) AS PO_STATUS_KEY,
    CASE
        WHEN "POD_END_EFF##1" IS NULL THEN 'open'
        ELSE 'closed'
    END AS PO_STAT
FROM PROD_RAW.EXCEL_QADDB.VW_POD_DET
WHERE LENGTH(TRIM(POD_NBR)) > 0 
  AND SUBSTR(TRIM(POD_NBR), 2, 1) IN ('0','1','2','3','4','5','6','7','8','9')
  AND LENGTH(TRIM(POD_PART)) > 0;

-- 对应EXCEL_PO_STATUS_UNSCHEDULED映射表
CREATE OR REPLACE VIEW dev_marts.purchasing.EXCEL_PO_STATUS_UNSCHEDULED AS
SELECT DISTINCT 
    PO_NBR AS PO_STATUS_KEY,
    CASE
        WHEN PO_STAT IS NULL THEN 'open'
        ELSE 'closed'
    END AS PO_STAT
FROM PROD_RAW.EXCEL_QADDB.VW_PO_MSTR;

再关联主表查询，生成目标字段：

SELECT 
    m.*,
    CASE
        WHEN m.POD_SCHED = '1' THEN s.PO_STAT
        ELSE u.PO_STAT
    END AS PO_STAT_DESC
FROM dev_marts.purchasing.EXCEL_REQ_PO_MSTR m
LEFT JOIN dev_marts.purchasing.EXCEL_PO_STATUS_SCHEDULED s
    ON m.PO_STATUS_KEY = s.PO_STATUS_KEY AND m.POD_SCHED = '1'
LEFT JOIN dev_marts.purchasing.EXCEL_PO_STATUS_UNSCHEDULED u
    ON m.POD_NBR = u.PO_STATUS_KEY AND m.POD_SCHED != '1';

方式2：子查询直接关联（无需预创建视图） #

如果不需要预存视图，可以用关联子查询直接实现逻辑：

SELECT 
    m.*,
    CASE
        WHEN m.POD_SCHED = '1' THEN (
            SELECT s.PO_STAT
            FROM (
                SELECT DISTINCT 
                    CONCAT_WS('|', POD_NBR, POD_LINE, POD_PART, POD_LOC, POD_PO_SITE, POD_DUE_DATE) AS PO_STATUS_KEY,
                    CASE WHEN "POD_END_EFF##1" IS NULL THEN 'open' ELSE 'closed' END AS PO_STAT
                FROM PROD_RAW.EXCEL_QADDB.VW_POD_DET
                WHERE LENGTH(TRIM(POD_NBR)) > 0 
                  AND SUBSTR(TRIM(POD_NBR), 2, 1) IN ('0','1','2','3','4','5','6','7','8','9')
                  AND LENGTH(TRIM(POD_PART)) > 0
            ) s
            WHERE s.PO_STATUS_KEY = m.PO_STATUS_KEY
        )
        ELSE (
            SELECT u.PO_STAT
            FROM (
                SELECT DISTINCT 
                    PO_NBR AS PO_STATUS_KEY,
                    CASE WHEN PO_STAT IS NULL THEN 'open' ELSE 'closed' END AS PO_STAT
                FROM PROD_RAW.EXCEL_QADDB.VW_PO_MSTR
            ) u
            WHERE u.PO_STATUS_KEY = m.POD_NBR
        )
    END AS PO_STAT_DESC
FROM dev_marts.purchasing.EXCEL_REQ_PO_MSTR m;

四、你的尝试代码问题说明 #

• 子查询未与主表关联，会返回所有匹配值而非当前行对应的结果，导致报错或返回错误数据
• 错误地将PO_STATUS_KEY/pod_nbr作为结果返回，而原逻辑是要获取映射后的状态值（open/closed）

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