如何用SQL筛选含指定值或值在条件范围的IBM i AS/400数据

业务场景 #

• 船舶选配部件套件数据存储在IBM i AS/400数据库的k23400t.Fcpstus表中，包含通用船舶部件编号、选配部件编号、关联属性条件（比如拖钓电机属性TMP）等字段
• 核算指定拖钓电机型号TMP='MK216'的选配总成本时，需要筛选两类记录：
  • 属性条件列显式包含TMP(*E)='MK216'的行
  • 属性条件列包含范围规则（如(TMP(*E)>='MK214' AND TMP(*E)<='MK346')）且MK216落在该范围内的行

现有问题 #

当前SQL仅通过模糊匹配筛选包含MK216或范围格式的记录，无法判断MK216是否实际落在属性值范围内，结果准确性无法保证。

解决方案 #

利用IBM i SQL的字符串解析与条件判断能力，提取属性范围的上下限，验证目标值是否在范围内。优化后的SQL如下：

CREATE TABLE qtemp.testIB AS (
    WITH temp AS (
        SELECT 
            pspmrn,
            pscmrn,
            imdsc,
            pscond,
            numqty,
            imlcos,
            -- 提取TMP属性的下限值
            REGEXP_SUBSTR(pscond, 'TMP\(\*E\)>=''([^'']+)''', 1, 1, 'i', 1) AS tmp_low,
            -- 提取TMP属性的上限值
            REGEXP_SUBSTR(pscond, 'TMP\(\*E\)<=''([^'']+)''', 1, 1, 'i', 1) AS tmp_high
        FROM k23400t.Fcpstus
        WHERE pspmrn = '23NZ19'
    )
    SELECT 
        pspmrn,
        pscmrn,
        imdsc,
        pscond,
        numqty,
        imlcos
    FROM temp
    WHERE 
        -- 匹配显式指定TMP='MK216'的记录
        pscond LIKE '%TMP(*E)=''MK216''%'
        OR 
        -- 匹配范围条件，且MK216落在上下限之间的记录
        (
            tmp_low IS NOT NULL 
            AND tmp_high IS NOT NULL 
            AND 'MK216' BETWEEN tmp_low AND tmp_high
        )
    ORDER BY pscond
) WITH DATA;

代码说明 #

• 用REGEXP_SUBSTR正则匹配从pscond字段里提取TMP属性的上下限值，适配TMP(*E)>='XXX'和TMP(*E)<='XXX'的格式
• 通过'MK216' BETWEEN tmp_low AND tmp_high验证目标型号是否在属性范围内
• 同时保留显式匹配的逻辑，确保两类符合要求的记录都能被筛选出来

直接汇总总成本的查询 #

如果不需要中间表，想直接计算选配功能的总成本，可以用以下聚合查询：

SELECT 
    SUM(numqty * imlcos) AS total_cost
FROM (
    WITH temp AS (
        SELECT 
            numqty,
            imlcos,
            REGEXP_SUBSTR(pscond, 'TMP\(\*E\)>=''([^'']+)''', 1, 1, 'i', 1) AS tmp_low,
            REGEXP_SUBSTR(pscond, 'TMP\(\*E\)<=''([^'']+)''', 1, 1, 'i', 1) AS tmp_high
        FROM k23400t.Fcpstus
        WHERE pspmrn = '23NZ19'
    )
    SELECT 
        numqty,
        imlcos
    FROM temp
    WHERE 
        pscond LIKE '%TMP(*E)=''MK216''%'
        OR 
        (
            tmp_low IS NOT NULL 
            AND tmp_high IS NOT NULL 
            AND 'MK216' BETWEEN tmp_low AND tmp_high
        )
) AS filtered_records;

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